Creating a report using a script
hi there
What i want to do is create a report targeting all the servers (disk name\disk size\disk used) at our head office only for servers not everybody else's machines
my problem is that we have different disk names such as the c:\, D:\, E:\, F:\ G:\ all different from each server.
i can however pull a report for all disk drives and it does give me a full report of everything but i only want the servers in our environment
please see report below:
SELECT MACHINE_DISKS.PERCENT_USED,MACHINE_DISKS.DISK_FREE,MACHINE_DISKS.NAME AS DISK_NAME,MACHINE_DISKS.DISK_SIZE,MACHINE_DISKS.DISK_USED,MACHINE.IP,GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,MACHINE.NAME AS SYSTEM_NAME FROM MACHINE_DISKS JOIN MACHINE ON (MACHINE.ID = MACHINE_DISKS.ID) LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE_DISKS.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden') GROUP BY MACHINE_DISKS.NAME ORDER BY PERCENT_USED
any help will be appreciated
thanks
Burton
Answers (1)
If your servers are all running a version of the OS with server in the name, then the easiest solution is to add a where clause:
SELECT MACHINE_DISKS.PERCENT_USED,MACHINE_DISKS.DISK_FREE,MACHINE_DISKS.NAME AS DISK_NAME,MACHINE_DISKS.DISK_SIZE,MACHINE_DISKS.DISK_USED,MACHINE.IP,GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,MACHINE.NAME AS SYSTEM_NAME FROM MACHINE_DISKS JOIN MACHINE ON (MACHINE.ID = MACHINE_DISKS.ID) LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE_DISKS.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
WHERE MACHINE.OS_NAME rlike "server"
GROUP BY MACHINE_DISKS.NAME ORDER BY PERCENT_USED
Comments:
-
hi Chucksteel
dude you the man
the report works and sees all drives that i needed
how do i upskill myself with SQL? i am still new in Kace especially when doing reports
any advise would help
much appreciated for your help - burtono 11 years ago -
I would first recommend downloading MySQL Workbench. It's a great tool to check out the SQL tables on the KBOX.
JVerbosk has an excellent blog post for getting started with SQL on the KBOX:
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
After that, I spend a lot of time with Google finding examples. - chucksteel 11 years ago -
