/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


K1000 reporting: custom 'uptime since last reboot' report?

06/12/2015 5202 views
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:
0 Comments   [ + ] Show comments

Comments


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

All 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
Purple Belt

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
Purple Belt

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ