Smart label for machines without a label in a group
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?
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?
0 Comments
[ + ] Show comments
Answers (1)
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')
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')
Please log in to answer
Posted by:
dchristian
12 years ago
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')
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')
Comments:
-
That works! Thanks. - chucksteel 12 years ago
