Stuck with SQL query - How do I exclude a group of machines by machine.name
Hello Ninjas,
I've spent far more time with this then I should have. . .
I use this query to gather software usage data from metered machines/applications.
SELECT IF(SC_SUITES.NAME is null, SC_APPS.Name, CONCAT(SC_APPS.Name, ' (' , SC_SUITES.NAME, ')')) AS SOFTWARE_NAME,
SAM_METER_DATA.VERSION,
COUNT(DISTINCT SMJ_APPS.MACHINE_ID) AS INSTALL_COUNT
FROM SAM_METER_DATA
JOIN CATALOG.SAM_CATALOG SC_APPS ON SAM_METER_DATA.TITLED_APPLICATION_ID = SC_APPS.ID
JOIN SAM_MACHINE_JT SMJ_APPS ON SMJ_APPS.SAM_CATALOG_ID = SC_APPS.ID AND SAM_METER_DATA.MACHINE_ID = SMJ_APPS.MACHINE_ID
LEFT JOIN MACHINE M ON M.ID = SAM_METER_DATA.MACHINE_ID
LEFT JOIN CATALOG.SAM_CATALOG SC_SUITES ON SC_SUITES.ID = SMJ_APPS.SUITE_ID
WHERE (SC_APPS.SAM_TYPE IN ('TITLED_APPLICATION','TITLED_SUITE') AND (SC_APPS.SOFTWARE_CATEGORY_ID <> 32))
AND SAM_METER_DATA.START > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID, SMJ_APPS.SAM_CATALOG_ID
ORDER BY SOFTWARE_NAME
In most cases this works well for me but, I have a request to exclude a group of MACHINES with a certain naming convention. I have tried multiple conditional lines, as in AND M.NAME NOT IN (blah blah blah) or, AND SMJ_APPS.MACHINE_ID IN (select ID from MACHINE where MACHINE.NAME NOT LIKE blah blah blah) so on. Regardless of how I format the condition or, what ID I try to use it fails to exclude the appropriate group of machines.
Any suggestions would be most appreciated.
I've spent far more time with this then I should have. . .
I use this query to gather software usage data from metered machines/applications.
SELECT IF(SC_SUITES.NAME is null, SC_APPS.Name, CONCAT(SC_APPS.Name, ' (' , SC_SUITES.NAME, ')')) AS SOFTWARE_NAME,
SAM_METER_DATA.VERSION,
COUNT(DISTINCT SMJ_APPS.MACHINE_ID) AS INSTALL_COUNT
FROM SAM_METER_DATA
JOIN CATALOG.SAM_CATALOG SC_APPS ON SAM_METER_DATA.TITLED_APPLICATION_ID = SC_APPS.ID
JOIN SAM_MACHINE_JT SMJ_APPS ON SMJ_APPS.SAM_CATALOG_ID = SC_APPS.ID AND SAM_METER_DATA.MACHINE_ID = SMJ_APPS.MACHINE_ID
LEFT JOIN MACHINE M ON M.ID = SAM_METER_DATA.MACHINE_ID
LEFT JOIN CATALOG.SAM_CATALOG SC_SUITES ON SC_SUITES.ID = SMJ_APPS.SUITE_ID
WHERE (SC_APPS.SAM_TYPE IN ('TITLED_APPLICATION','TITLED_SUITE') AND (SC_APPS.SOFTWARE_CATEGORY_ID <> 32))
AND SAM_METER_DATA.START > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID, SMJ_APPS.SAM_CATALOG_ID
ORDER BY SOFTWARE_NAME
In most cases this works well for me but, I have a request to exclude a group of MACHINES with a certain naming convention. I have tried multiple conditional lines, as in AND M.NAME NOT IN (blah blah blah) or, AND SMJ_APPS.MACHINE_ID IN (select ID from MACHINE where MACHINE.NAME NOT LIKE blah blah blah) so on. Regardless of how I format the condition or, what ID I try to use it fails to exclude the appropriate group of machines.
Any suggestions would be most appreciated.
1 Comment
[ + ] Show comment
-
can you give us more detail about the list of machines that you want to exclude. Or maybe the query that you feel should work but does not? - thewruck 7 years ago
Answers (2)
Please log in to answer
Posted by:
JasonEgg
7 years ago
Odd that the sub-select options didn't work for you (i.e. "...IN (select ID from...)" ). Are you sure you get the desired devices if you only run the sub-select?
Another option: create a smart label based on the naming convention, then include the label in your query ("AND LABEL.NAME != [sub-label]"). The one caveat to this is you need ensure the "evaluation order" of smart labels happens correctly. Change these settings in Home > Label Management > Smart Labels > Choose Action > Order Labels > Device Smart Labels.
Comments:
-
Hi Jason,
Yes the sub-select, when run directly, captures the machines I need to exclude. When I tested that originally it only added to my frustration ;) Regarding using LABEL I'll look into that.
Thanks - jmarotto 7 years ago -
When using the IN condition your query needs to return a comma separated list. - chucksteel 7 years ago
Posted by:
chucksteel
7 years ago
You should be able to add a
AND M.NAME not like "whatever%" to the main query.
SELECT IF(SC_SUITES.NAME is null, SC_APPS.Name, CONCAT(SC_APPS.Name, ' (' , SC_SUITES.NAME, ')')) AS SOFTWARE_NAME,
SAM_METER_DATA.VERSION,
COUNT(DISTINCT SMJ_APPS.MACHINE_ID) AS INSTALL_COUNT,
GROUP_CONCAT(DISTINCT(M.NAME))
FROM SAM_METER_DATA
JOIN CATALOG.SAM_CATALOG SC_APPS ON SAM_METER_DATA.TITLED_APPLICATION_ID = SC_APPS.ID
JOIN SAM_MACHINE_JT SMJ_APPS ON SMJ_APPS.SAM_CATALOG_ID = SC_APPS.ID AND SAM_METER_DATA.MACHINE_ID = SMJ_APPS.MACHINE_ID
LEFT JOIN MACHINE M ON M.ID = SAM_METER_DATA.MACHINE_ID
LEFT JOIN CATALOG.SAM_CATALOG SC_SUITES ON SC_SUITES.ID = SMJ_APPS.SUITE_ID
WHERE (SC_APPS.SAM_TYPE IN ('TITLED_APPLICATION','TITLED_SUITE') AND (SC_APPS.SOFTWARE_CATEGORY_ID <> 32))
AND SAM_METER_DATA.START > DATE_SUB(NOW(), INTERVAL 30 DAY)
and M.NAME not like "lib%"
GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID, SMJ_APPS.SAM_CATALOG_ID
ORDER BY SOFTWARE_NAMEThis excludes machines that begin with lib. I also included a column that will show the machine names where the software was found to help debug.
Comments:
-
Thanks for your time on this Chuck. It didn't resolve my challenge but provided more information for me to look into. The query does gather up the machines by name for each metered application and, when the NOT LIKE condition is added, it excludes the machines as desired. What I found is, the machines group and exclude by name but the INSTALL_COUNT doesn't change.
I happened across a similar result while I was messing around with this a few days back and that lead me to using the AND SMJ_APPS.MACHINE_ID IN (select ID from MACHINE where MACHINE.NAME NOT LIKE blah blah blah) condition. At least now I know I was headed down the right rabbit hole. - jmarotto 7 years ago-
Interesting. In my environment the install count does change when I exclude machines. I'm not sure what the difference might be. - chucksteel 7 years ago
-
Hey Chuck,
Wanted to follow up on this to advise that, I'm an idiot ;)
My original added condition - AND SAM_METER_DATA.MACHINE_ID IN (select ID from MACHINE where MACHINE.NAME NOT LIKE 'xxxx%') actually does work. The issue was and, what I failed to confirm was the actual number of machines in the exclude. Inventory list several hundred. This morning I looked at this group only to find Metering hadn't been enabled on most. This is why my COUNT didn't appear to be working. - jmarotto 7 years ago
