I'm looking for some guidance in creating a label to target system uptime. It seems as if I have to get a bit creative to do so as there are some inconsistencies in the format of how the uptime is reported depending on where I check. My goal is to have a Smart Label for systems that have a reported uptime of, say, over 45 days.

For an example of the problem I'm encountering - if I say:
"Uptime" ">" "45 days"
I will get in the results systems that are only up for "5" days - since it doesn't pull that number as "05" days.

If you've done something similar or have any suggestions please respond!
Answer Summary:
Manually edit a Smart Label with the SQL code below (adjust to your needs): 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) > 45 ))
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2
This should work:

1. Create a new label (ex "long reported uptimes"
2. Create a smart label where Uptime > 45
3. Edit the smart label
4. Replace "Uptime > '45'" with "left(UPTIME, locate(',', UPTIME) -1) > 45"

In the database it looks like the uptime is stored as days,hours:minutes.

The SQL finds the comma (locate), cuts off everything after & including the comma(left) and then compares the result to the number 45 instead of the string '45'.

The whole SQL for the smart label is:

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) > 45 ))
Answered 03/20/2012 by: Thomas.Babcock
Blue Belt

  • Thanks! This seems to have done it. Testing great so far. I'm not noticing any false results.
  • This works for me; I just have a follow-up: That query above gives 4 results: computer, last user logged in, <something>, unix time format of last time the machine logged in. Anyone know what that 3rd item is??
Please log in to comment

Answers

0
I have been looking into this same thing. I have tried everything I could think of. I'll let you know if I find anything.
Answered 03/20/2012 by: WGM_Jeff
Fourth Degree Black Belt

  • This works for me; I just have a follow-up: That query above gives 4 results: computer, last user logged in, <something>, unix time format of last time the machine logged in. Anyone know what that 3rd item is??
Please log in to comment
Answer this question or Comment on this question for clarity