john.rickard

SCCM Report on Collection



4 posts in this topic

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

Share this post


Link to post
Share on other sites


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

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now