Solved

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

Posted on 2013-12-05
2
1,715 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A procedure for exporting installed hotfix details of remote computers using powershell
Viewers will learn how the fundamental information of how to create a table.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

730 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