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

 

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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

 

Answered 10/16/2012 by: chucksteel
Red Belt

  • 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
  • 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.
  • Thanks chuck

    appreciate it
Please log in to comment
Answer this question or Comment on this question for clarity