Guys

Maybe it's just me , but I am trying to pull back a report for my systems on the field "uptime since last reboot"

Is there an easy way to find out what this field is called so I can make a report. I'm entering "Uptime_since_last_reboot" but it doesn't like that.

 

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
I look at from a little different angle.  My report uses the last shut down date and the machine is connected to kace.

SELECT MACHINE.NAME AS SYSTEM_NAME, LAST_SHUTDOWN, SC.CLIENT_CONNECTED  FROM MACHINE  LEFT JOIN KBSYS.KUID_ORGANIZATION KUID_ORG ON KUID_ORG.KUID = MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID AND substring(SCHEMA(),4) = KUID_ORG.ORGANIZATION_ID WHERE ((SC.CLIENT_CONNECTED = '1'))  ORDER BY LAST_SHUTDOWN
Answered 06/20/2014 by: SMal.tmcc
Red Belt

Please log in to comment
0

Thanks ChuckSteel

 

In terms of adding your code into my code - what should I add the "LPAD..." part?

It doesn't seem to like it when I edit my code.

Answered 06/23/2014 by: FergieMan
Third Degree Green Belt

  • Replace this line:
    , UPTIME
    with these:
    , LPAD(SUBSTRING_INDEX(UPTIME, ',', 1),3,"0") as DAYS, LPAD(SUBSTRING(UPTIME, LOCATE(',', UPTIME) + 1, LOCATE(':', UPTIME) - LOCATE(',', UPTIME) - 1),2,"0") as HOURS, LPAD(SUBSTRING_INDEX(UPTIME, ':', -1),2,"0") as MINUTES
    • Thanks Chuck

      Could I amend the script at the bottom to only show servers which have a reboot time of over 30 days?

      I had "and UPTIME < date_sub(now(), interval 30 day" but it is ignoring this
      • Since the UPTIME field isn't stored as a timestamp you can't use the date commands like you normally would. To find entries greater than 30 days you would need to use this:
        LPAD(SUBSTRING_INDEX(UPTIME, ',', 1),3,"0") > 30
Please log in to comment
0
Ok , update.....here is my code

select LABEL.NAME as LABEL_NAME, MACHINE.NAME as MACHINE_NAME, MACHINE.USER_LOGGED as USER_LOGGED
, ORG1.MACHINE.ID
, UPTIME

from ORG1.MACHINE
left outer join ORG1.MACHINE_LABEL_JT on MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
left outer join ORG1.LABEL on MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
where LABEL.TYPE !='hidden' and (LABEL.NAME = 'M_Desktop' or LABEL.NAME = 'M_Laptop') 
and UPTIME < date_sub(now(), interval 7 day)

but the Uptime is not getting created as "days/hours/mins"

Is there a SQL command to do this?


Answered 06/20/2014 by: FergieMan
Third Degree Green Belt

  • The table stores the data as days,hours so to translate it into days, hours and minutes you need to use the substring command:

    SELECT ID, NAME, UPTIME,
    LPAD(SUBSTRING_INDEX(UPTIME, ',', 1),3,"0") as DAYS,
    LPAD(SUBSTRING(UPTIME, LOCATE(',', UPTIME) + 1, LOCATE(':', UPTIME) - LOCATE(',', UPTIME) - 1),2,"0") as HOURS,
    LPAD(SUBSTRING_INDEX(UPTIME, ':', -1),2,"0") as MINUTES
    FROM ORG1.MACHINE;

    This is from one of the stock reports on my KBox although I changed the padding a little bit.
Please log in to comment
Answer this question or Comment on this question for clarity

Share