create a collection

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

create a collection

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

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

Page: [1]

Message

<< Older Topic Newer Topic >>

create a collection - 9/12/2008 12:42:56 AM

alokin123

Posts: 28
Score: 0
Joined: 8/7/2008
Status: offline

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%"

Post #: 1

RE: create a collection - 9/12/2008 8:21:55 AM

mhudson

Posts: 541
Score: 12
Joined: 4/1/2007
From: College Station, TX
Status: offline

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

_____________________________

Matthew Hudson
http://sms-hints-tricks.blogspot.com/
http://www.sccm-tools.com

(in reply to alokin123)

Post #: 2

RE: create a collection - 9/12/2008 9:39:54 AM

fault

Posts: 32
Score: 2
Joined: 7/21/2008
Status: offline

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

(in reply to mhudson)

Post #: 3

RE: create a collection - 9/12/2008 5:04:56 PM

alokin123

Posts: 28
Score: 0
Joined: 8/7/2008
Status: offline

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

(in reply to fault)

Post #: 4

RE: create a collection - 9/14/2008 8:50:10 PM

alokin123

Posts: 28
Score: 0
Joined: 8/7/2008
Status: offline

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?

(in reply to alokin123)

Post #: 5