I have a report that runs and tell me all the computers that have 20% or less disk space left. the only problem is the report only gives me the computer name, system description , MAC and IP addresses. I would also like the report to include just how much space is left on the drives in question

 

here is my current sql query. I dont really know anything about sql but if someone could make a sql query that would accomplish this, I can copy and paste it.

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
                       UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
                  from ORG1.MACHINE
                  LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
                 where (((  (1  in (select 1 from ORG1.MACHINE_DISKS where MACHINE.ID = MACHINE_DISKS.ID and MACHINE_DISKS.DISK_FREE < '20%')) ) OR RAM_TOTAL = '80%'))

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2

Will this work for you? It's not getting all the info that "*" will get you. 

select MACHINE.NAME, MACHINE.SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC,
MACHINE_DISKS.PERCENT_USED, MACHINE_DISKS.DISK_FREE,MACHINE_DISKS.NAME as DISK_NAME
from ORG1.MACHINE
join MACHINE_DISKS on (MACHINE.ID = MACHINE_DISKS.ID)
left join KBSYS.KUID_ORGANIZATION on KUID_ORGANIZATION.KUID=MACHINE.KUID
left join KBSYS.SMMP_CONNECTION on SMMP_CONNECTION.KUID = MACHINE.KUID and KUID_ORGANIZATION.ORGANIZATION_ID = 1
where MACHINE_DISKS.PERCENT_USED > '80' or RAM_TOTAL = '80%'
order by MACHINE.NAME
Answered 07/11/2012 by: dugullett
Red Belt

Please log in to comment

Answers

0

See if this works for you.  Also, if you want to understand the queries a little more, check out this primer on MySQL for the K1000:

Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

John

___________________________

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS,
MACHINE_DISKS.DISK_FREE,MACHINE_DISKS.NAME as DISK_NAME
from ORG1.MACHINE
join MACHINE_DISKS on (MACHINE.ID = MACHINE_DISKS.ID)
left join KBSYS.KUID_ORGANIZATION on KUID_ORGANIZATION.KUID=MACHINE.KUID
left join KBSYS.SMMP_CONNECTION on SMMP_CONNECTION.KUID = MACHINE.KUID and KUID_ORGANIZATION.ORGANIZATION_ID = 1
where MACHINE_DISKS.DISK_FREE < '20%' or RAM_TOTAL = '80%'
order by MACHINE_DISKS.DISK_FREE

Answered 07/05/2012 by: jverbosk
Red Belt

  • If you don't want the report sorted by free disk space, just remove the last line.

    John
  • Thank you very mich, it worked very good, except it did not give the system name, in place of system name was the Disk information. Im not sure if that can be changed or not? but thank you very much for such a quick reply.
  • I ran the query on my K1000 and the system name is listed in the Name column (sixth column, not counting the # column). The hard drive info should be in the last two columns.

    John
  • I definetly did something wrong then, Im going to go back the the primer you wrote for the second time as suggested and give it another shot. though all I did was copy and paste the sql code you wrote into a new sql report. the only feild I did not know what to populate in was the "breaks on cloums feild"
  • All you need to do is copy & paste the code. I might suggest deleting the existing report completely and make one from scratch - I've found there's a bug that messes up the columns when you keep tweaking/replacing the code. Start from scratch with a new SQL report and it should work just fine.

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