Creating A Label For System Uptime
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!
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!
0 Comments
[ + ] Show comments
Answers (2)
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 ))
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 ))
Please log in to answer
Posted by:
Thomas.Babcock
12 years ago
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 ))
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 ))
Comments:
-
Thanks! This seems to have done it. Testing great so far. I'm not noticing any false results. - jegolf 12 years ago
-
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?? - mrpadilla 11 years ago
Posted by:
WGM_Jeff
12 years ago
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.
Comments:
-
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?? - mrpadilla 11 years ago
