K1000: Computer list by software smart label
Hello all,
Trying to figure out how to do this in reporting. I have a software smart label called 'visio -tb' that is used to pull all the 'real' versions of visio. It is:
SELECT ID FROM SOFTWARE WHERE ((((((((( SOFTWARE.PUBLISHER like '%microsoft%') AND SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio 2010') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Visio Professional 2010') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Visio Professional 2013') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio Professional 2007') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Visio Premium 2010') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio Standard 2007') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio Professional 2003') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio Professional 2003')
The above script works fine. What I need to be able to do is to pull a list of machines that are pulled from the 'visio -tb' smart label, with computer name, user name, and software version. My total SQL skills are installing MS SQL, the above script was just editing and existing one.
Thanks a head of time!
Answers (1)
I'm not sure how you could explicitly reference the smart label in the SQL, but you can use the SQL to build the report query.
I did some quick testing and connecting table you are needing is MACHINE_SOFTWARE_JT.
You may need to play around and tweak a bit, but here might be a general idea of what you are looking for.
select MACHINE.NAME, MACHINE.USER_NAME, SOFTWARE.DISPLAY_NAME from MACHINE left join MACHINE_SOFTWARE_JT on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID left join SOFTWARE on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID where SOFTWARE.PUBLISHER = "Adobe Systems, Inc."
I didn't have any Visio installed, so I substituted Adobe. If the above works in your environment, then you can delete the where in my statement and replace it with the where statement you have listed above. You can try and paste what I have below and let me know how it works out. I am rather new to this also, but I am pretty sure this will work.
select MACHINE.NAME, MACHINE.USER_NAME, SOFTWARE.DISPLAY_NAME from MACHINE left join MACHINE_SOFTWARE_JT on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID left join SOFTWARE on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID WHERE ((((((((( SOFTWARE.PUBLISHER like '%microsoft%')
AND SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio 2010') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Visio Professional 2010') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Visio Professional 2013') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio Professional 2007') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Visio Premium 2010') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio Standard 2007') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio Professional 2003') OR SOFTWARE.DISPLAY_NAME = 'Microsoft Office Visio Professional 2003')
