Skip to content

SCCM Report on Collection

Featured Replies

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
  • Author

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.