/build/static/layout/Breadcrumb_cap_w.png

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 ))

0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 6 years ago
Red Belt
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.

Comments:
  • Thank you - this worked! - cetoth 6 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ