smart label on machines that haven't rebooted in 2 weeks
Is there a way in kace to find only machines that have been on for 2 weeks. I want to be able to create a smart label that finds machines that are on for more then 2 weeks and restart them. I've tried the greater than option but it appears that the uptime value in kace is a string. Here's the sql I tried and failed at:
SELECT
CONCAT(SUBSTRING_INDEX(UPTIME, ',', 1), ' days, ', SUBSTRING(UPTIME, LOCATE(',', UPTIME) + 1, LOCATE(':', UPTIME) - LOCATE(',', UPTIME) - 1), ' hours, ', SUBSTRING_INDEX(UPTIME, ':', -1), ' minutes') AS UPTIME,
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, NAME, IP,
LPAD(SUBSTRING_INDEX(UPTIME, ',', 1),3,"0") as DAYS,
LPAD(SUBSTRING(UPTIME, LOCATE(',', UPTIME) + 1, LOCATE(':', UPTIME) - LOCATE(',', UPTIME) - 1),3,"0") as HOURS,
LPAD(SUBSTRING_INDEX(UPTIME, ':', -1),3,"0") as MINUTES
FROM MACHINE MACHINE
Where datepart(dd, UPTIME) >= 14
ORDER BY DAYS, HOURS, MINUTES
Answers (1)
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.IP, USER_LOGGED, LAST_REBOOT, LAST_SYNC FROM MACHINE WHERE DateDiff(CurDate(), LAST_REBOOT) > 14 ORDER BY LAST_REBOOT
This is SQL I wrote a while back that should do what you want.
Comments:
-
Hrm... I'm not sure how to make that work on my kbox as I have multiple orgs. I'm getting an SQL syntax error when I tried,
select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS
from ORG2.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 2
where (( DateDiff(CurDate(), LAST_REBOOT) > 30 ORDER BY LAST_REBOOT))
ERROR IS:
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY LAST_REBOOT))' at line 5] in EXECUTE( "select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME, UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS from ORG2.MACHINE LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 2 where (( DateDiff(CurDate(), LAST_REBOOT) > 14 ORDER BY LAST_REBOOT))") - awingren 11 years ago-
Nevermind, I was over-thinking it... this worked fine:
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.IP, USER_LOGGED, LAST_REBOOT, LAST_SYNC FROM MACHINE WHERE DateDiff(CurDate(), LAST_REBOOT) > 30 ORDER BY LAST_REBOOT - awingren 11 years ago
