K1000 Reporting (Uptime Since Last Reboot)
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
Answers (3)
Please log in to answer
Posted by:
SMal.tmcc
9 years ago
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
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
Posted by:
FergieMan
9 years ago
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.
Comments:
-
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 - chucksteel 9 years ago-
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 - FergieMan 9 years ago-
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 - chucksteel 9 years ago
Posted by:
FergieMan
9 years ago
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?
Comments:
-
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. - chucksteel 9 years ago