/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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 ))
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

Answers

1
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) > 10
This 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.
Answered 11/06/2017 by: chucksteel
Red Belt

  • Thank you - this worked!
Please log in to comment

Share