Hello. I've created a report from the reporting wizard which shows a list of all specified systems and their uptime since last reboot. This works great (except for the fact that uptime's read as text and sorted thusly). Looking for some uptime guidance if anybody has any. Questions:

  1. Does anybody have a SQL query report for uptime reported 15 days or greater? If so how does it calculate the uptime if Kace stores this value as text?
  2. Is it at all possible to send automated individual e-mails to each of these listed end users as a variable through reporting or service desk?
  3. Doing a smart label search for uptime since last reboot by 15 days (or some combination) doesn't work at all because of Kace seeing this listed time as text. Anybody found a way to create a simple smart label this way?
Thank you,
Ben
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
To create a smart label you need to modify the SQL a little bit. 
Create the smart label as normal and save it. 
Browse to Home, Label Management, Smart Labels
Click the smart label you just created
Click the Edit SQL button
Delete everything after the word WHERE
Replace it with the following:
left(UPTIME, locate(',', UPTIME) -1) > 15
Save the smart label

Notifying users is more difficult. I would probably opt to use a script that targets this smart label and prompts the user to restart their computer. Depending on how aggressive you want to be you could schedule it to run daily or hourly and give the user to snooze or cancel. You could possibly have the script even restart the computer if no one is logged into the computer.


Answered 06/15/2015 by: chucksteel
Red Belt

Please log in to comment

Answers

1
Thank you both. I actually used a combination of a report created with the reporting wizard that targets a smart label.

The smart label called "LT: uptime only" I took from Chucks instructions to show all computers with uptime greater than 15 days:

Smart label SQL:
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE  WHERE
left(UPTIME, locate(',', UPTIME) -1) > 15


Then I created a report wizard which shows uptime and targets this label. Here's the resultant SQL code of the complete report:

SELECT MACHINE.NAME AS SYSTEM_NAME, USER_FULLNAME, (CONCAT(SUBSTRING_INDEX(UPTIME, ',', 1), ' days, ', SUBSTRING(UPTIME, LOCATE(',', UPTIME) + 1, LOCATE(':', UPTIME) - LOCATE(',', UPTIME) - 1), ' hours, ', SUBSTRING_INDEX(UPTIME, ':', -1), ' minutes')) AS UPTIME  FROM MACHINE  WHERE ((( exists  (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'LT: uptime only')) ))  ORDER BY UPTIME desc

This report shows a simple table with only the system name, user full name, and uptime since last reboot (greater than 15 days).
Answered 06/19/2015 by: bens401
Orange Senior Belt

Please log in to comment
1
I also will automate a Kace Alert agent pop up window for these computers that haven't been rebooted over 15 days, or longer. I created an alert which targets the smart label I created above. I'll probably set it to run all weekend or maybe after 5pm or something. Thanks again for the tip.
Answered 06/19/2015 by: bens401
Orange Senior Belt

Please log in to comment
1
For part of your question - here's my SQL for a smart label reporting an uptime of 60 days you can 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) > 60 ))
Answered 06/13/2015 by: jegolf
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity