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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×