Add label to existing query
Hi,
I would like to add existing label to following query. Where would the join need to go?
select (CASE WHEN MACHINE.MANUAL_ENTRY = 1 THEN 'Manually added device'
WHEN MACHINE.MANUAL_ENTRY = 2 THEN 'Manually added device'
WHEN MACHINE.MANUAL_ENTRY = 3 and UNIX_TIMESTAMP(MACHINE.LAST_SYNC) > (UNIX_TIMESTAMP()-(SELECT VALUE*60 FROM SETTINGS WHERE NAME ='AGENTLESS_INVENTORY_INTERVAL')) THEN 'Agentless device'
WHEN MACHINE.MANUAL_ENTRY = 3 and UNIX_TIMESTAMP(MACHINE.LAST_SYNC) <= (UNIX_TIMESTAMP()-(SELECT VALUE*60 FROM SETTINGS WHERE NAME ='AGENTLESS_INVENTORY_INTERVAL'))
THEN 'Unable to reach this agentless device'
WHEN CLIENT_CONNECTED =1 THEN 'Agent-managed device'
WHEN CLIENT_CONNECTED =0 THEN 'Unable to reach this agentless device'
ELSE 'Unable to reach this agentless device'
END) AS 'Status',MACHINE.NAME as SYSTEM_NAME,MACHINE.IP, OS_NAME,MACHINE.OS_VERSION,MACHINE.SERVICE_PACK, UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS, MACHINE.USER_DOMAIN,
MACHINE.USER,MACHINE.CS_MANUFACTURER,MACHINE.CS_MODEL,MACHINE.CS_DOMAIN,
MACHINE.PROCESSORS
,GROUP_CONCAT(MACHINE_DISKS.NAME) as DNAME from MACHINE left join MACHINE_DISKS on MACHINE.ID = MACHINE_DISKS.ID 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
GROUP BY MACHINE.ID order by LAST_SYNC_SECONDS DESC
Thanks.
I would like to add existing label to following query. Where would the join need to go?
select (CASE WHEN MACHINE.MANUAL_ENTRY = 1 THEN 'Manually added device'
WHEN MACHINE.MANUAL_ENTRY = 2 THEN 'Manually added device'
WHEN MACHINE.MANUAL_ENTRY = 3 and UNIX_TIMESTAMP(MACHINE.LAST_SYNC) > (UNIX_TIMESTAMP()-(SELECT VALUE*60 FROM SETTINGS WHERE NAME ='AGENTLESS_INVENTORY_INTERVAL')) THEN 'Agentless device'
WHEN MACHINE.MANUAL_ENTRY = 3 and UNIX_TIMESTAMP(MACHINE.LAST_SYNC) <= (UNIX_TIMESTAMP()-(SELECT VALUE*60 FROM SETTINGS WHERE NAME ='AGENTLESS_INVENTORY_INTERVAL'))
THEN 'Unable to reach this agentless device'
WHEN CLIENT_CONNECTED =1 THEN 'Agent-managed device'
WHEN CLIENT_CONNECTED =0 THEN 'Unable to reach this agentless device'
ELSE 'Unable to reach this agentless device'
END) AS 'Status',MACHINE.NAME as SYSTEM_NAME,MACHINE.IP, OS_NAME,MACHINE.OS_VERSION,MACHINE.SERVICE_PACK, UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS, MACHINE.USER_DOMAIN,
MACHINE.USER,MACHINE.CS_MANUFACTURER,MACHINE.CS_MODEL,MACHINE.CS_DOMAIN,
MACHINE.PROCESSORS
,GROUP_CONCAT(MACHINE_DISKS.NAME) as DNAME from MACHINE left join MACHINE_DISKS on MACHINE.ID = MACHINE_DISKS.ID 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
GROUP BY MACHINE.ID order by LAST_SYNC_SECONDS DESC
Thanks.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
9 years ago
You need to add two join statements:
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')
These go with the other join statements that are in your query. In general when writing a SQL query the syntax is:
SELECT things that you want to see
FROM the main table
JOIN to other tables
WHERE these criteria match
Once you have the two joins added to the query you can add a statement to your where clause to filter by LABEL.NAME = "Label here"
The query would then look something like this:
select (CASE WHEN MACHINE.MANUAL_ENTRY = 1 THEN 'Manually added device'
WHEN MACHINE.MANUAL_ENTRY = 2 THEN 'Manually added device'
WHEN MACHINE.MANUAL_ENTRY = 3 and UNIX_TIMESTAMP(MACHINE.LAST_SYNC) > (UNIX_TIMESTAMP()-(SELECT VALUE*60 FROM SETTINGS WHERE NAME ='AGENTLESS_INVENTORY_INTERVAL')) THEN 'Agentless device'
WHEN MACHINE.MANUAL_ENTRY = 3 and UNIX_TIMESTAMP(MACHINE.LAST_SYNC) <= (UNIX_TIMESTAMP()-(SELECT VALUE*60 FROM SETTINGS WHERE NAME ='AGENTLESS_INVENTORY_INTERVAL'))
THEN 'Unable to reach this agentless device'
WHEN CLIENT_CONNECTED =1 THEN 'Agent-managed device'
WHEN CLIENT_CONNECTED =0 THEN 'Unable to reach this agentless device'
ELSE 'Unable to reach this agentless device'
END) AS 'Status',MACHINE.NAME as SYSTEM_NAME,MACHINE.IP, OS_NAME,MACHINE.OS_VERSION,MACHINE.SERVICE_PACK, UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS, MACHINE.USER_DOMAIN,
MACHINE.USER,MACHINE.CS_MANUFACTURER,MACHINE.CS_MODEL,MACHINE.CS_DOMAIN,
MACHINE.PROCESSORS
,GROUP_CONCAT(MACHINE_DISKS.NAME) as DNAME
WHEN MACHINE.MANUAL_ENTRY = 2 THEN 'Manually added device'
WHEN MACHINE.MANUAL_ENTRY = 3 and UNIX_TIMESTAMP(MACHINE.LAST_SYNC) > (UNIX_TIMESTAMP()-(SELECT VALUE*60 FROM SETTINGS WHERE NAME ='AGENTLESS_INVENTORY_INTERVAL')) THEN 'Agentless device'
WHEN MACHINE.MANUAL_ENTRY = 3 and UNIX_TIMESTAMP(MACHINE.LAST_SYNC) <= (UNIX_TIMESTAMP()-(SELECT VALUE*60 FROM SETTINGS WHERE NAME ='AGENTLESS_INVENTORY_INTERVAL'))
THEN 'Unable to reach this agentless device'
WHEN CLIENT_CONNECTED =1 THEN 'Agent-managed device'
WHEN CLIENT_CONNECTED =0 THEN 'Unable to reach this agentless device'
ELSE 'Unable to reach this agentless device'
END) AS 'Status',MACHINE.NAME as SYSTEM_NAME,MACHINE.IP, OS_NAME,MACHINE.OS_VERSION,MACHINE.SERVICE_PACK, UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS, MACHINE.USER_DOMAIN,
MACHINE.USER,MACHINE.CS_MANUFACTURER,MACHINE.CS_MODEL,MACHINE.CS_DOMAIN,
MACHINE.PROCESSORS
,GROUP_CONCAT(MACHINE_DISKS.NAME) as DNAME
from MACHINE
left join MACHINE_DISKS on MACHINE.ID = MACHINE_DISKS.ID
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
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')
WHERE LABEL.NAME = "Label goes here"
GROUP BY MACHINE.ID order by LAST_SYNC_SECONDS DESC
GROUP BY MACHINE.ID order by LAST_SYNC_SECONDS DESC
