Smart Label Creator vs SQL Query
I needed to search for machines having a Software Title (SPSSStatistics) with two different versions (20 and 25), so I created an Advanced Search that searched Software Titles for "SPSSStatistics" and Software Version begins with "20" and Software Version begins with "25", and when I got odd results, realized (and later confirmed by reading https://www.itninja.com/question/computer-smart-label-based-off-software-titles-and-software-version-number-less-than) that the Advanced Search and Smart Label Creator interprets this search as "machines having a Software Title of 'SPSSStatistics' and ANY software that has a version beginning with 20 or 25".
Of course that's completely not what I wanted.
So, I conclude that the Advanced Search and Smart Label Creator are too imprecise for a user to properly craft a search and the user will expect to get X and will instead unknowingly get corrupted response Y. I suspect that other smart labels and reports I have are incorrectly providing bad data because of this setup.
Do y'all recommend SQL Queries as being more reliable? Seems like a terribly steep learning curve. Do y'all have any wisdom to impart about this? Am I understanding correctly, or am I simply using Search incorrectly?
Thanks!
Of course that's completely not what I wanted.
So, I conclude that the Advanced Search and Smart Label Creator are too imprecise for a user to properly craft a search and the user will expect to get X and will instead unknowingly get corrupted response Y. I suspect that other smart labels and reports I have are incorrectly providing bad data because of this setup.
Do y'all recommend SQL Queries as being more reliable? Seems like a terribly steep learning curve. Do y'all have any wisdom to impart about this? Am I understanding correctly, or am I simply using Search incorrectly?
Thanks!
1 Comment
[ + ] Show comment
-
Did you try that last example by Troy_Grey in that link you posted? It appeared to work in my environment, testing with chrome and firefox. It was very easy to modify and run. - five. 5 years ago
Answers (1)
Please log in to answer
Posted by:
chucksteel
5 years ago
Yes, it is important to sanity check your smart labels, they can easily return the incorrect results. I generally create SQL queries for reports, here is one that should find machines with SPSS 20 or SPSS 25:
SELECT MACHINE.NAME AS SYSTEM_NAME,
USER_LOGGED,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED ,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_VERSION SEPARATOR '\n') AS SOFTWARE_DISPLAY_VERSION_GROUPED
FROM MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID)
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID)
WHERE (DISPLAY_NAME like "%SPSS%Statistics%" and DISPLAY_VERSION like "23%")
or (DISPLAY_NAME like "%SPSS%Statistics%" and DISPLAY_VERSION like "25%")
GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
