Jump to content


Recommended Posts

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

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

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...