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!

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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')

 

 

 

Answered 12/16/2013 by: kscott
White Belt

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

Share