Solved

SCCM Report - Update Installed software on a specific computer Report to run against Collection

Posted on 2013-12-05
2
1,684 Views
Last Modified: 2013-12-18
Hello SCCM Experts - Any assistance you can provide would be greatly appreciated!

I am trying to edit the current SCCM 2007 Report - "2E - Installed software on a specific computer" so it will report against a collection than a single computer.

The current report SQL Report is below, what should I update that it will allow the use of a collection?  What are your thoughts?

Select distinct
v_R_System_Valid.Netbios_Name0 AS "Computer Name",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS "Product Name",
"Publisher" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher
End,
"Version" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion  = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion
End,
"Language" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 < 0) then 'Unknown'
Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 as varchar)
End,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallType0 as "Installation Type",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName AS "Product Family",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName AS "Product Category",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductID0 AS "ProductID",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID as "Software ID",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CM_DSLID0 AS "DSL ID",
"Installed Location" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0 IS NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0
End,
"Install Source" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0
End,
"Uninstall String" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0
End,
"Install Date" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 is NULL ) then 'Unknown'
Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 as varchar)
End,
"Registered User" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0
End
FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED
INNER JOIN v_R_System_Valid on v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID
Where v_R_System_Valid.Netbios_Name0 = @Name
order by v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName, Publisher, Version
0
Comment
Question by:rmessing171
2 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 39702036
Firstly, you could change all the " to ' to get the query to work correctly in T-SQL

Then if your current single machine is denoted by
Where v_R_System_Valid.Netbios_Name0 = @Name

Open in new window

you could change that to a list of names like this:
where v_R_System_Valid.Netbios_Name0 in (@Name1, @Name2, @Name3)

Open in new window

where you arrange to provide each name to the query.

If you're doing this in, for example, an SSRS report and you are also changing your input from a single name in a drop-list to a drop-list where you can select multiple names then what you'll get a string of server names, like 'Fred,Bill,Jim', instead of a single value for @name like 'Fred' for the server.  If that is the case, but the query should remain the same, you'll need a slightly different way of doing things so that you can accept the list of servers.

Please answer stating what kind of input the query will get with multiple servers (with example!) .

hth

Mike
0
 

Author Comment

by:rmessing171
ID: 39706489
Hi Mike - Thank you for the quick reply.  Please forgive me I am new to SCCM and reporting.  I do not know what you mean by changing all of the  "to" to get the query working.

Could I change the query code so I could reference a Collection rather than listing several computernames?
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question