SCCM Report on Collection

[john.rickard]

Afternoon Everyone,

 

I am trying to create a report in CM 2012 R2 which will report the OUs which machines are a member of. I want to limit this so that I can select a specific collection and it will report that.

 

I have found various things online and followed quite a few, but I am in no way a SQL master

 

Would it be possible for someone to have a gander at the attached and see what I am going wrong with - I think it is something to do with altering the Getting_the_ous query to encompass the collectionID.

 

When I run it at the moment I can pick a collection and I can then click view report, this runs but doesnt limit it to a collection and hows the everything!

 

Thanks

John

 

 

Getting the OUs

 

SELECT DISTINCT

dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_R_System.User_Name0, MAX(dbo.v_RA_System_SystemOUName.System_OU_Name0) AS Expr1, dbo.v_GS_COMPUTER_SYSTEM.Description0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_PC_BIOS.SerialNumber0

FROM dbo.v_GS_COMPUTER_SYSTEM INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN

dbo.v_RA_System_SystemOUName ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_RA_System_SystemOUName.ResourceID INNER JOIN

dbo.v_GS_PC_BIOS ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_GS_PC_BIOS.ResourceID

GROUP BY dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_R_System.User_Name0, dbo.v_GS_COMPUTER_SYSTEM.Description0,

dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_PC_BIOS.SerialNumber0

Getting the collection list

Select * from V_collection

[GarthMJ]

Give this a try.

http://tinyurl.com/mfkof7d

[john.rickard]

Excellent - that works brilliantly...

 

I have just changed it to use my parameter of @CollID and it works a treat... Now playing to add an IP address column

Thanks

[GarthMJ]

BTW, I almost never recommend adding IP address to any report. only in very rare cases will I recommend it... It caught too many problems..