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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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.

Answered 05/09/2013 by: Ben M
Ninth Degree Black Belt

  • 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))")
    • 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
      • This seems to work for a report but not for a smart label... any thoughs?
      • This works fine as a smart label for me. How are you setting up the smart label?
Please log in to comment
Answer this question or Comment on this question for clarity

Share