K1000 reporting: custom 'uptime since last reboot' report?
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:
- 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?
- 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?
- 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
0 Comments
[ + ] Show comments
Answers (5)
Answer Summary:
Please log in to answer
Posted by:
bens401
8 years ago
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).
Posted by:
bens401
8 years ago
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.
Posted by:
jegolf
8 years ago
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 ))
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 ))
Posted by:
sam240
2 years ago
This report is will very handy still. Can someone help me adjust the filtering? I'm only looking for Windows 10 machines and not macs.
I tried creating a smart label with only windows 10 but it doesn't like it.
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE ((OS_NAME like '%Microsoft Windows 10 Pro x64%') AND WHERE
left(UPTIME, locate(',', UPTIME) -1) > 15
Posted by:
chucksteel
8 years ago
Top Answer
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.