Hello. I'm trying to create a smart label to organize Windows machines which have not rebooted in X number of days. I have a report that appears to work correctly to generate those same results. I took the SQL from the report and copied it to a temporarily created smart label I created and saved it, but it doesn't appear to be applying correctly.

Wondered if anyone has suggestions about taking SQL from a report and porting it over to also work as a smart label?

Thanks.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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.
Answered 04/26/2011 by: dchristian
Red Belt

Please log in to comment
0
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?
Answered 04/26/2011 by: timantheos
Orange Senior Belt

Please log in to comment
0
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
Answered 04/27/2011 by: dchristian
Red Belt

Please log in to comment
0
Doesn't seem to work either.
Answered 04/27/2011 by: timantheos
Orange Senior Belt

Please log in to comment
0
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.
Answered 04/27/2011 by: Capt.Morgan
Orange Belt

Please log in to comment
0
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.
Answered 06/13/2011 by: timantheos
Orange Senior Belt

Please log in to comment
0
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)
Answered 06/14/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 06/14/2011 by: craig.thatcher
Orange Belt

Please log in to comment
0
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.
Answered 06/14/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity