Add/Remove Programs dump for all computers in a collection (Full Version)

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



Message

enieft -> Add/Remove Programs dump for all computers in a collection (10/8/2008 2:59:29 PM)

I am looking to get a add/remove programs inventory on all computers in a specific collection or is this possible by querying by name (select all devices with name AB, AC and get add/remove inventory information).

Looking for a sql statement or suggestions.

Thanks,
Eric



gjones -> RE: Add/Remove Programs dump for all computers in a collection (10/9/2008 8:56:26 AM)

try this http://smsug.ca/blogs/garth_jones/archive/2008/10/09/list-arp-by-pcs-with-name-like.aspx



enieft -> RE: Add/Remove Programs dump for all computers in a collection (10/9/2008 12:04:51 PM)

what happens though if I dont have the view:  or should I be putting this query into sms rather than query analyzer?
dbo.v_Add_Remove_Programs




jnelson993 -> RE: Add/Remove Programs dump for all computers in a collection (10/9/2008 12:16:46 PM)

SMS 2003, right?  He's giving you valid SQL code (even if it does use v_GS_COMPUTER_SYSTEM instead of V_R_SYSTEM :) ... so what do you mean "don't have the view"  It's a view that comes with SMS...so unless you've deleted it, it's got to be there.  Are you sure the SMS db is the active DB?  Either select it from the top, or run:

USE SMS_XYZ
GO

(change SMS_XYZ to your SMS database name first) then run his query from query analyzer



enieft -> RE: Add/Remove Programs dump for all computers in a collection (10/9/2008 1:28:10 PM)

dbo.v_Add_Remove_Programs is the view that I cannot see.

Eric



gjones -> RE: Add/Remove Programs dump for all computers in a collection (10/9/2008 1:36:45 PM)

Use this instead.

SELECT
ARP.ProdID0,
ARP.DisplayName0,
ARP.Version0,
CS.Name0
FROM
dbo.v_GS_ADD_REMOVE_PROGRAMS ARP,
dbo.v_GS_COMPUTER_SYSTEM CS
WHERE
ARP.ResourceID = CS.ResourceID
AND CS.Name0 Like 'AB%'



enieft -> RE: Add/Remove Programs dump for all computers in a collection (10/9/2008 2:08:59 PM)

cool that worked.  Now what I was looking for was a full list rather than each pc seperate,  however it's an easy fix in excel to get this information.

Thanks again folks!

Eric



jnelson993 -> RE: Add/Remove Programs dump for all computers in a collection (10/9/2008 2:45:21 PM)

One little caveat, that view is for 32-bit machines only.  If you have any x64 machines, they won't be represented by that view...that's why the dbo.v_Add_Remove_Programs view is handy.

So perhaps, just do this to get the 32-bit AND 64-bit data together

SELECT
   arp.ProdID0,
   arp.DisplayName0,
   arp.Version0
FROM
   dbo.v_GS_Add_Remove_Programs ARP
WHERE
   arp.resourceID IN (SELECT ResourceID FROM dbo.v_FullCollectionMembership WHERE CollectionID = @CollectionID)
UNION
SELECT
   arp.prodID0,
   arp.DisplayName0,
   arp.Version0
FROM
   dbo.v_GS_Add_Remove_Programs_64 arp
WHERE
   arp.resourceID IN (SELECT ResourceID FROM dbo.v_FullCollectionMembership WHERE CollectionID = @CollectionID)
ORDER BY
   DisplayName0    



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.3440018