Custom SQL query in Smart Label not working
So I'm a bit confused on what specifically required in the query for a smart label to work. I'm trying to make a smart label that includes all computers that have a certain NIC. This isn't something that can be done from the smart label "wizard" so I created this query:
SELECT MACHINE.ID FROM MACHINE,MACHINE_NICS as N WHERE MACHINE.ID = N.ID AND N.NIC like '%Mobile Broadband Mini-card%';
I've tested it and it appears to return what I want but I can't get the smart label to work. From what I've read from searches is you just need MACHINE.ID for the smart label to work.
3 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
mgomez
10 years ago
I was able to figure it out, I have another label that is based on the IP address so I modified that one. Here's what it looks like:
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 (( (1 in (select 1 from ORG1.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.NIC like '%Mobile Broadband Mini-card%')) ))
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME, UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS from ORG1.MACHINE - grayematter 10 years ago