We have applied labels indicating locations to the majority of computers in our inventory. All of the location labels are in a label group "Location".

I have a SQL statement that will find machines that don't have a label in the Location group:
SELECT * FROM
(SELECT MACHINE.*, group_concat(distinct LABEL.NAME separator ",") AS LABELS,
group_concat(distinct PARENTLABEL.NAME separator ",") AS PARENTLABELS
FROM ORG1.MACHINE
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
LEFT JOIN LABEL_LABEL_JT on LABEL_LABEL_JT.CHILD_LABEL_ID = LABEL.ID
LEFT JOIN LABEL PARENTLABEL on (PARENTLABEL.ID = LABEL_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
GROUP BY MACHINE.ID) as MACHINESWITHLABELS
WHERE PARENTLABELS not like '%Location%'
ORDER BY NAME

This works to create a report that shows all of the machines that don't have a "Location" label applied, but I'd like to have a smart label which would be applied to those machines. If I try using the above code in the smart label it doesn't work. Does anyone have suggestions?
Answer Summary:
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 MACHINE.ID NOT IN (SELECT M.ID FROM LABEL CHILD, LABEL_LABEL_JT LL, LABEL PARENT, MACHINE M, MACHINE_LABEL_JT ML WHERE CHILD.ID = LL.CHILD_LABEL_ID AND LL.LABEL_ID = PARENT.ID AND M.ID = ML.MACHINE_ID AND ML.LABEL_ID = CHILD.ID AND PARENT. NAME = 'LOCATION')
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

3
Does this work?


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 MACHINE.ID NOT IN (SELECT M.ID
FROM LABEL CHILD,
LABEL_LABEL_JT LL,
LABEL PARENT,
MACHINE M,
MACHINE_LABEL_JT ML
WHERE CHILD.ID = LL.CHILD_LABEL_ID
AND LL.LABEL_ID = PARENT.ID
AND M.ID = ML.MACHINE_ID
AND ML.LABEL_ID = CHILD.ID
AND PARENT. NAME = 'LOCATION')
Answered 03/28/2012 by: dchristian
Red Belt

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