create a collection (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

alokin123 -> create a collection (9/12/2008 12:42:56 AM)

i would like to create a collection of all machines that have any version other than MS Project 2007 and MS Visio 2007. i.e. A machine for collections with project 2003 and less, another collection with visio 2003 and less and one combined of those 2 collections. Is someone able to point me in the right direction on what criteria i should be using and looking for? Most machines have Visio viewer but i dont want that returned. I'm just playing around with it at the moment with just getting machines with Project but i dont think i am quite there. If anyone can offer some advise that would be great: 

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client 
from 
SMS_R_System inner join SMS_G_System_CollectedFile on SMS_G_System_CollectedFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CollectedFile.FileName like "%proj%"
 
				  
	          

	        

	        
 
	          

			  	  		
	        

		  

				
	  

	
	  
			
  













  
 
    
	

	  
 
	    
		 

		    

			  
				  mhudson ->  RE: create a collection  (9/12/2008 8:21:55 AM)
			  
			

	        

			  

		          
				   Are you not sure if you have XP and 97 versions out there?  See if this gets you started: 
 You should look at what is based on Add/Remove  
  
  
 Collection with Project 2003 and less 
 Query 1:Project 2003 
 select SMS_G_System_COMPUTER_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%project professional 2003%" 
  
 Query 2:Project 2003 
 select SMS_G_System_COMPUTER_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%project professional XP%" 
  
 --------------------------------------------- 
 Better way of doing it.  This will create a collection of machines that don't yet have Project 2007.  This is because OFfice 2007 can uninstall the older versions of its applications. 
  
 select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_G_System_COMPUTER_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Office project professional 2007%") 
  
 ----------------------------- 
 If you want to combine collections then you have to place all Queries into the same collection 
				  
	          		
	        

	        
 
	          

			  	  		
	        

		  

				
	  

	
	  
			
  













  
 
    
	

	  
 
	    
		 

		    

			  
				  fault ->  RE: create a collection  (9/12/2008 9:39:54 AM)
			  
			

	        

			  

		          
				   I like to use the SMS_G_System_ADD_REMOVE_PROGRAMS table (hardware inventory cycle) as Matthew suggested, but if you want to do it via the SMS_G_System_SoftwareFile table (software inventory cycle), this WQL query should get you going: 
  
 select SMS_R_System.NetbiosName, SMS_G_System_SoftwareFile.FileVersion, SMS_G_System_SoftwareFile.FileName  
 from SMS_G_System_SoftwareFile  
 inner join SMS_R_System  
 on SMS_R_System.ResourceId = SMS_G_System_SoftwareFile.ResourceID  
 where (SMS_G_System_SoftwareFile.FileName = "WINPROJ.EXE" or SMS_G_System_SoftwareFile.FileName = "VISIO.EXE") and SMS_G_System_SoftwareFile.FileVersion NOT LIKE "12%" 
  
 You can break up the query into separate ones if you want one for WINPROJ.EXE and VISIO.EXE. You won't capture any Visio Viewer installs with this query as it doesn't share the same executable name (strictly speaking it doesn't actually have its own executable but hooks into Internet Explorer instead). 
  
				  
	          		
	        

	        
 
	          

			  	  		
	        

		  

				
	  

	
	  
			
  













  
 
    
	

	  
 
	    
		 

		    

			  
				  alokin123 ->  RE: create a collection  (9/12/2008 5:04:56 PM)
			  
			

	        

			  

		          
				   thanks for those. I will give them a shot and let you know how it  goes 
				  
	          		
	        

	        
 
	          

			  	  		
	        

		  

				
	  

	
	  
			
  













  
 
    
	

	  
 
	    
		 

		    

			  
				  alokin123 ->  RE: create a collection  (9/14/2008 8:50:10 PM)
			  
			

	        

			  

		          
				   ok. that is all working fine. I don't suppose there is anyway to see  license and sale origin channel? I just want to see if a product is covered under a users MSDN liscence or our companies select license. I know reporting has the Microsoft Liscence Ledger but i am a bit confused by it. If i do a report Microsoft Liscence Ledger by Sales Channel for Project and drill down it gives me the machines and the Inventoried quantity  and the channel. If i dig down deeper to display the machine names, it gives the Product ID which is something like this: 52207-270-0861382-02980. Can i assume that is the product id for Project or for the OS or for something else? 
				  
	          		
	        

	        
 
	          

			  	  		
	        

		  

				
	  

	
	  
			
  












  
 
    
	

	  
 
	      
		  Page:  [1] 
		  
	  

	

    		
  





    

	 
	  Valid CSS!
	 

    







  

	Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 
	ANSI
  



0.21875