Terrible with building reports..can someone help me with this one?

[BooThatman]

I am so bad with writing reports in SMS 2007... If anyone can help me it would be greatly appreciated.

 

See if you can create a SCCM report that will identify

 

1. All “Microsoft” Product Names

 

2. Publisher

 

3. Version

 

4. Category Name

 

5. Instance Count

 

 

SCCM 2007 SP2 - 4.00.6487.2000

 

 

If anyone responds with RTFM my answer is...I AM lol Still confusing but I am trying to get a grasp on it.

[Peter van der Woude]

If you have Asset Intelligence enabled (http://technet.microsoft.com/en-us/library/cc161988.aspx), you should have enough options (which you are looking for) with those default reports.

[BooThatman]

I am looking at the Asset Intelligence section now. Appreciate the info.

[BooThatman]

This one seems to cover all 5 points.

 

 

if(@Publisher IS NULL)

begin

select @Publisher = ''

end

if(@ProductName IS NULL)

begin

select @ProductName = ''

end

if(@Version IS NULL)

begin

select @Version = ''

end

if(@Publisher <> '')

begin

select @Publisher = '%' + @Publisher + '%'

end

if(@ProductName <> '')

begin

select @ProductName = '%' + @ProductName + '%'

end

if(@Version <> '')

begin

select @Version = @Version + '%'

end

Select TOP (convert(bigint,@NumberOfRows))

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName as [Product Name],

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher as [Publisher],

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion as [Version],

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName as [Family Name],

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName as [Category Name],

count(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID) as [instance Count],

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID as [software ID],

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwarePropertiesHash0 as [software Properties Hash],

@CollectionID as [Collection ID]

from v_GS_INSTALLED_SOFTWARE_CATEGORIZED with (NOLOCK)

Where v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID IN

(

select distinct v_FullCollectionMembership.ResourceID

FROM v_FullCollectionMembership inner join v_R_System_Valid

ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID

where CollectionID = @CollectionID

) AND

(@ProductName = '' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName like @ProductName)

and (@Publisher ='' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher like @Publisher)

and (@Version ='' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion like @Version )

group by v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName,

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher,

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion,

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName,

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName,

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID,

v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwarePropertiesHash0

order by [instance Count] desc, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName asc, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName asc

 

Boom. This is the one. Thank you sir. You are the man. I found the collection that produced results and now I believe I have exactly what I need. =]

 

There is one thing that I left off my first post.

6. GROUP BY Base and/or MAJCOM names

 

Can you tell me how to add a column that groups it by base or "majcom?"