SQL Smart Group for Devices that haven't reboot
I'm trying to make a smart group to show those devices that have the following specific criteria:
- have Windows OS
- have the 'target' label
- do NOT have the labels 'test' or 'test2'
- have not been reboot in 30 days or more
This is what I have so far, and it's not populating at all. I'm not an SQL expert, so I've peiced this together but am aware that I probably have some glaring issues. Any help would be appreciated.
SELECT
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID AS TOPIC_ID
FROM
MACHINE
LEFT JOIN
MACHINE_LABEL_JT ON MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
LEFT JOIN
LABEL ON MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
WHERE
OS_NAME LIKE '%Microsoft Windows%'
AND LABEL.NAME LIKE '%target%'
AND LABEL.NAME <> 'test'
AND LABEL.NAME <> 'test2'
AND LAST_REBOOT <= DATE_SUB(NOW(), INTERVAL 30 DAY)
Answers (2)
To troubleshoot, you may want to start with the SQL statement below, to see if it picks up the expected devices in the output that have not rebooted in the last 30 days.
select ID, NAME, CAST(LAST_REBOOT as DATETIME) from MACHINE where LAST_REBOOT <= DATE_SUB(NOW(), INTERVAL 30 DAY)
If this works as expected, you can add to the SQL statement to meet your needs.
Please update us with the results.
Comments:
-
Thanks KevinG! This works and returns all the devices that haven't been reboot in 30 days which is great. The problem now is that I can't seem to modify it to include or exclude labels (whatever I try just doesn't work). I'm now trying to create another smart label that filters these out devices on this new label - hopefully that works. - seanboy 11 months ago
-
Post your latest SQL to see if we can spot an issue. - KevinG 11 months ago
I’m pretty sure you could build this using the smart label wizard,
OS contains Windows
label names = to whatever label you want
reboot not within last 30 days
Group
label names != to label 1 and
label names != to label 2
will probably do it, no need for an sql struggle ;o)
Comments:
-
Thanks for the comment. Alas there is no 'reboot not within...' or 'last reboot' option in the smart label criteria. There is an 'uptime since last reboot' option, but that doesn't work no matter what value I put in there (not sure if its in seconds, minutes or days, but it doesnt seem to matter). Thanks though! - seanboy 11 months ago
-
So why not build the label with the wizard and then add in the final part of the Where statement? AND LAST_REBOOT <= DATE_SUB(NOW(), INTERVAL 30 DAY) - Hobbsy 11 months ago
-
I think I finally figured it out. Looks like the problem was that the Smart label couldnt reference other smart labels until I changed the order number of those labels (which I didnt know was a thing until now!). https://support.quest.com/de-de/kb/4300500/smart-label-referencing-other-smart-labels-fails
Thanks for your help everyone! - seanboy 11 months ago
