KACE Product Support

One thing i learned (the hard way[:D]) is that there can be nothing after the where statement in smart label SQL.

Post your code so we can take a look.

I do have a line after the WHERE clause. Here is the code (it's modified from a similar report, but works as a report):

SELECT MACHINE.NAME AS SYSTEM_NAME, ASSET_DATA_5.FIELD_32 AS FIELD_32,
ASSET_DATA_5.FIELD_33 AS FIELD_33, ASSET_DATA_5.FIELD_37 AS FIELD_37,
LAST_REBOOT, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_REBOOT) as LAST_REBOOT_TIME
FROM MACHINE
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE (ASSET_DATA_5.FIELD_37 LIKE '%active%') AND (OS_NAME LIKE '%windows%')
HAVING LAST_REBOOT_TIME > 2592000

Right now the HAVING clause is what sets time time to measure last reboot. If it's actually not possible to have this clause in there then we'd need another way to set that measure?

Maybe not the most efficient query but see if this works.

SELECT M.*
FROM MACHINE M,
(SELECT MACHINE.NAME,
Unix_timestamp(NOW()) - Unix_timestamp(LAST_REBOOT) AS
LAST_REBOOT_TIME
FROM MACHINE
LEFT JOIN ASSET
ON ASSET.MAPPED_ID = MACHINE.ID
AND ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET_DATA_5
ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE OS_NAME LIKE '%windows%'
AND ASSET_DATA_5.FIELD_37 LIKE '%active%'
HAVING LAST_REBOOT_TIME > 2592000) WORK
WHERE M.NAME = WORK.NAME

Doesn't seem to work either.

This works fine:


SELECT MACHINE.NAME AS SYSTEM_NAME,
LAST_REBOOT
FROM MACHINE
WHERE LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 7 DAY)


It's very basic but it does work for me in a Smart Label I quickly made to show all PCs that have been rebooted in 7+ Days.

That doesn't appear to be working for me either. Though I did edit the number of days to 30. Additionally I would need to filter it for an asset value we have called 'active', so either way it wouldn't suffice unfortunately.

A smart label needs to return MACHINE.ID so it would be like this:

SELECT MACHINE.ID FROM MACHINE
JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE
ASSET_DATA_5.FIELD_37 LIKE '%active%'
AND OS_NAME LIKE '%windows%' AND LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 7 DAY)

We use the following for our machines with an UPTIME greater than 2 days.

select MACHINE.ID
from ORG1.MACHINE
where UPTIME>5 and OS_NAME not like '%Server%' AND OS_NAME NOT LIKE '%OS X%'

we have this tied to a script that forces a reboot at 2 am to enforce patching. you'll see that we exclude servers to minimize unexpected outages. I've double checked the code and output and the SQL works for the 5.1.x agents and 5.2.x agents even though the formatting of the data is held differently in the field.

To take it a step further you can have smart labels that are conditional upon labels. Say you have labels for your servers and your OS X machines separately called "Servers" and "OS X Machines" respectively, now you can update those labels when the criteria changes for servers or os x machines.

select MACHINE.ID
from ORG1.MACHINE
LEFT JOIN
(select MACHINE_ID from MACHINE_LABEL_JT ML
JOIN LABEL L ON ML.LABEL_ID=L.ID and L.NAME RLIKE '^(Servers|OS X machines)$' ) MLABELS ON MACHINE.ID=MLABELS.MACHINE_ID
where UPTIME>5
and MLABELS.MACHINE_ID IS NULL /* machines not in those labels */


Lastly, you can even have them conditional upon other smart labels if you set the evaluation order of the smart labels appropriately. So your label for "Servers" could be a smart label that looks at OS_NAME or a more rigorous qualification of what a server is.