Smart Label for computers that haven't restarted in x days
I've searched through the site for the answer and the SQL code I've tried based on my searches haven't worked. I would like a smart label to show computers that are online and have a last reboot of more than 10 days. I've tried this code which works well in a report, but doesn't work as a smart label. The results listed in a smart label include computers whose last reboot was 2 hours ago.
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.IP, USER_LOGGED, LAST_REBOOT, LAST_SYNC FROM MACHINE WHERE DateDiff(CurDate(), LAST_REBOOT) > 10 ORDER BY LAST_REBOOT
I really don't know much about SQL, but I'm trying to learn. I've also tried this SQL and it doesn't pull up the correct machines. I have also tried this SQL and get different results than the code above but still not complete results. Same with the next set of code. Any help would be appreciated. I have a simple script that will restart these machines, but I want to base it on the smart label.
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE
left(UPTIME, locate(',', UPTIME) -1) > 10.
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 (( left(UPTIME, locate(',', UPTIME) -1) > 10 ))
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 (( left(UPTIME, locate(',', UPTIME) -1) > 10 ))
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
Here is the query I use:
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE left(UPTIME, locate(',', UPTIME) -1) > 10This is the same as one of your queries listed above, although you have a period at the end of yours, that might just be your post, and not the actual query.
There isn't really a need to add the check for systems online because the smart label is only applied during an inventory, and by definition the computer will be online when it runs inventory.
Comments:
-
Thank you - this worked! - cetoth 7 years ago
