How to customize an email report on K1000
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%'))
Answers (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
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
Comments:
-
If you don't want the report sorted by free disk space, just remove the last line.
John - jverbosk 12 years ago -
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. - jamminRed 12 years ago
-
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 - jverbosk 12 years ago -
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" - jamminRed 12 years ago
-
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 - jverbosk 12 years ago
