Does anyone have a sql query I can use to create a smart label that lists systems that have been up for more than 7 days? I've found a few other references from 7 or 8 years ago but I can't get them to work.
I have tried the advice here (https://www.itninja.com/question/creating-a-label-for-system-uptime), here (https://www.itninja.com/blog/view/kace-last-reboot-and-last-sync-smart-machine-label), and here (https://www.itninja.com/blog/view/workstation-uptime-24-hours-smart-label) to no avail. Either I get query error or the results are wrong. This is what I have thus far. I created it by making a basic smart label looking for windows 10 OS_Name and then modifying it to also include (LAST_REBOOT > DATE_SUB(now(5), interval 24 hour)) as found here (https://www.itninja.com/blog/view/kace-last-reboot-and-last-sync-smart-machine-label)
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE ((OS_NAME like '%windows 10%'))
AND (LAST_REBOOT > DATE_SUB(now(5), interval 24 hour))
Any help is greatly appreciated.
Answers (1)
Top Answer
Try this,
SELECT
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID
FROM MACHINE
WHERE (((TIMESTAMP(LAST_REBOOT) <= NOW() AND TIMESTAMP(LAST_REBOOT) > DATE_SUB(NOW(),INTERVAL 7 DAY))))
But I have to say that there are a couple of timestamps that you could use rather that LAST_REBOOT, ie LAST_INVENTORY and LAST_SHUTDOWN
