Add "specify by collection" or "group by 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:

Add "specify by collection" or "group by collection"

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting >> Add "specify by collection" or "group by collection"

Page: [1]

Message

<< Older Topic Newer Topic >>

Add "specify by collection" or "group by... - 7/8/2009 12:35:30 PM

khunter

Posts: 84
Score: 0
Joined: 9/12/2006
Status: offline

Hi All,

There are a number of existing SMS 2003 reports that I would like to see for specific collections, or maybe group the totals by collection so I could drill down into each collection's results. I copied existing code to add a prompt to collect the Collection ID, but I'm not savvy enough with SQL to add the transaction query properly.

I (think I) know I want to add a join to the end of the existing SQL statement for the report w/ a where CollectionID = @ID.

What's the best view to pull CollectionID from? Is this as simple as adding a couple of lines of sql at the bottom of the existing statement or is it more complicated than I realize?

The reports I'm most interested in breaking out are #61 (Count of computers w/ specific file), #106 (Count of OS and service packs),

Thanks for any help any of you can provide!!

KH

Post #: 1

RE: Add "specify by collection" or "grou... - 7/8/2009 12:59:26 PM

khunter

Posts: 84
Score: 0
Joined: 9/12/2006
Status: offline

I actually was able to get a clone of report 61 to work, after looking at some other reports that let you specify collection. There's only one twist I would like to add, and that is to add the Collection name as a column in the report. I can't see right off hand how to do it. Below is my SQL statement:

select SYS.Netbios_Name0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath
From v_GS_SoftwareFile SF
join v_R_System SYS on SYS.ResourceID = SF.ResourceID
JOIN v_FullCollectionMembership FCM on FCM.ResourceID=SF.ResourceID
Where SF.FileName LIKE @variable and FCM.CollectionID=@CollID
ORDER BY SYS.Netbios_Name0

(in reply to khunter)

Post #: 2

RE: Add "specify by collection" or "grou... - 7/8/2009 1:45:44 PM

cnackers

Posts: 681
Score: 36
Joined: 9/22/2006
From: Madison, WI
Status: offline

you just need to look at v_fullcollectionmemebership, i believe there is a name table in there, or find another table that has it and a join it in your query

_____________________________

My Blog:(SMS,SCCM/ConfigMgr,OSD,MDT,Overall Systems Management)
http://myitforum.com/cs2/blogs/cnackers/default.aspx

(in reply to khunter)

Post #: 3

Page: [1]

All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting >> Add "specify by collection" or "group by collection"

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts

0.203