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.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
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 
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

Answered 09/11/2014 by: chucksteel
Red Belt

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