Jump to content
MartynKeigher

Storage Report

Recommended Posts

Hey all,

 

Here is some SQL in regards to (%) Storage used, at Locations. This is more useful data for clients that DON'T have virtualization, or underlying storage space NOT consumed by the servers (for the most part), and you'll see what I mean when you see the results and what they mean. This has helped us once or twice in very certain/specific situations, so ... yeah.. take it or leave it, What you do with it is up to you. :)

 

Percent of Drive Space used per location - ALL:

 

SELECT Clients.Name, Locations.Name, SUM(Size-Free)/1024 AS `GB Used` ,SUM(Free)/1024 AS `GB Free`, SUM(Size-Free)/SUM(Size)*100 AS `Percent Used` FROM ((Drives JOIN Computers USING(ComputerID)) JOIN Locations USING (LocationID)) JOIN Clients ON Computers.ClientID=Clients.CLientID GROUP BY Computers.LocationID ORDER BY `Percent Used` DESC;

 

Percent of Drive Space used per location - INTERNAL DRIVES ONLY:

 

SELECT Clients.Name, Locations.Name, SUM(Size-Free)/1024 AS `GB Used` ,SUM(Free)/1024 AS `GB Free`, SUM(Size-Free)/SUM(Size)*100 AS `Percent Used` FROM ((Drives JOIN Computers USING(ComputerID)) JOIN Locations USING (LocationID)) JOIN Clients ON Computers.ClientID=Clients.CLientID WHERE Drives.Internal=1 GROUP BY Computers.LocationID ORDER BY `Percent Used` DESC;

Percent of Drive Space used per location - INTERNAL DRIVES ONLY (Servers):

 

SELECT Clients.Name, Locations.Name, SUM(Size-Free)/1024 AS `GB Used` ,SUM(Free)/1024 AS `GB Free`, SUM(Size-Free)/SUM(Size)*100 AS `Percent Used` FROM ((Drives JOIN Computers USING(ComputerID)) JOIN Locations USING (LocationID)) JOIN Clients ON Computers.ClientID=Clients.CLientID WHERE Drives.Internal=1 AND computers.OS LIKE '%server%' GROUP BY Computers.LocationID ORDER BY `Percent Used` DESC;

 

Percent of Drive Space used per location - INTERNAL DRIVES ONLY (Workstations):

 

SELECT Clients.Name, Locations.Name, SUM(Size-Free)/1024 AS `GB Used` ,SUM(Free)/1024 AS `GB Free`, SUM(Size-Free)/SUM(Size)*100 AS `Percent Used` FROM ((Drives JOIN Computers USING(ComputerID)) JOIN Locations USING (LocationID)) JOIN Clients ON Computers.ClientID=Clients.CLientID WHERE Drives.Internal=1 AND computers.OS NOT LIKE '%server%' GROUP BY Computers.LocationID ORDER BY `Percent Used` DESC;

 

Regards,

Share this post


Link to post
Share on other sites

How are you using this? i was trying to put into a script using SQL execute and then output into a csv but i think the syntex in that instance is different for example

 

SELECT Clients.Name, Locations.Name, SUM(Size-Free)/1024 AS `GB Used` ,SUM(Free)/1024 AS `GB Free`, SUM(Size-Free)/SUM(Size)*100 AS `Percent Used` FROM ((Drives JOIN Computers USING(ComputerID)) JOIN Locations USING (LocationID)) JOIN Clients ON Computers.ClientID=Clients.CLientID WHERE Drives.Internal=1 AND computers.OS NOT LIKE '%server%' GROUP BY Computers.LocationID ORDER BY `Percent Used` DESC;

INTO OUTFILE 'C:\\LTShare\\Inventory\\diskspace.csv'

Share this post


Link to post
Share on other sites

dsinton44,

Try to remove the ";" before "INTO OUTFILE"

but anyway it will export the sqlresult each raw in only one excel cell.

I think after removing the ";" from the end you have to separate them using:

 

INTO OUTFILE 'C:\\LTShare\\Inventory\\diskspace.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

so you will have them as a columns in csv file

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...