locked
Add Boundary info to SCCM Report RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Technet!

    I am having a heck of a time trying to add a Boundary info to my SCCm report.  We modified the configuration.mof file to search for all systems that have explicit local admins added, and so far this query works (This queries a collection of Windows 7):

    select distinct Name0 as 'Computer Name', substring(AccountName0,charindex('Domain=',Accountname0)+8,(charindex('Name=',Accountname0)-charindex('Domain=',Accountname0)-10)) as 'Domain Name', substring(AccountName0,len(AccountName0)-charindex('"',reverse(AccountName0),2)+2,charindex('"',reverse(AccountName0),2)-2) as 'User Name'

    from v_GS_SYSTEM
    INNER JOIN v_GS_LocalAdmins ON v_GS_SYSTEM.ResourceID = v_GS_LocalAdmins.ResourceID


    JOIN v_FullCollectionMembership FCM on v_gs_system.ResourceID = FCM.ResourceID

    where (AccountName0 not like '%Administrator%' AND AccountName0 not like '%Domain Admins%')


    and

    FCM.CollectionID='SDG00382'

    Just trying to add a column in the report to show which AD boundary the client belongs to.  Can anyone help with the SQL statement to add that please?  Thanks in advanced!

    Tuesday, July 10, 2012 2:55 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    This worked for me with the appropriate collectionID;

    select distinct v_GS_SYSTEM.Name0 as 'Computer Name',A.AD_Site_Name0, substring(AccountName0,charindex('Domain=',Accountname0)+8,(charindex('Name=',Accountname0)-charindex('Domain=',Accountname0)-10)) as 'Domain Name', substring(AccountName0,len(AccountName0)-charindex('"',reverse(AccountName0),2)+2,charindex('"',reverse(AccountName0),2)-2) as 'User Name'
    from v_GS_SYSTEM
    INNER JOIN v_GS_LocalAdmins ON v_GS_SYSTEM.ResourceID = v_GS_LocalAdmins.ResourceID
    INNER JOIN v_R_System A ON v_GS_SYSTEM.ResourceID=A.ResourceID

    JOIN v_FullCollectionMembership FCM on v_gs_system.ResourceID = FCM.ResourceID
    where (AccountName0 not like '%Administrator%' AND AccountName0 not like '%Domain Admins%')

    and
    FCM.CollectionID='SDG00382'

    Also see; http://social.technet.microsoft.com/Forums/en-CA/configmgrinventory/thread/6e1000cf-283a-4958-b6d4-ec43d82c8376

    Simon Brouillard

    • Marked as answer by DonD1976 Wednesday, July 11, 2012 2:39 PM
    Wednesday, July 11, 2012 2:32 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    try this http://eskonr.com/2012/07/sccm-report-computer-information-with-boundary-added/

    Please click on "vote as Helpful" if you feel this post helpful to you.

    Eswar Koneti | My Tech blog: www.eskonr.com | Linkedin: Eswar Koneti

    Tuesday, July 10, 2012 7:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Eswar,

    Thank you for the reply!  I appreciate the effort tremendously! 

    I tried this SQL statement in my report (modified my Collection ID):

    select distinct v_GS_SYSTEM.Name0 as ‘Computer Name’,A.AD_Site_Name0, substring(AccountName0,charindex(‘Domain=’,Accountname0)+8,(charindex(‘Name=’,Accountname0)-charindex(‘Domain=’,Accountname0)-10)) as ‘Domain Name’, substring(AccountName0,len(AccountName0)-charindex(‘"’,reverse(AccountName0),2)+2,charindex(‘"’,reverse(AccountName0),2)-2) as ‘User Name’
    from v_GS_SYSTEM
    INNER JOIN v_GS_LocalAdmins ON v_GS_SYSTEM.ResourceID = v_GS_LocalAdmins.ResourceID
    INNER JOIN v_R_System A ON v_GS_SYSTEM.ResourceID=A.ResourceID

    JOIN v_FullCollectionMembership FCM on v_gs_system.ResourceID = FCM.ResourceID
    where (AccountName0 not like ‘%Administrator%’ AND AccountName0 not like ‘%Domain Admins%’)

    and
    FCM.CollectionID=’SMSC1003′

    and I get the infamous Exclamation point when I try to apply-- perhaps a syntax error? 

    Don D.

    Tuesday, July 10, 2012 2:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This worked for me with the appropriate collectionID;

    select distinct v_GS_SYSTEM.Name0 as 'Computer Name',A.AD_Site_Name0, substring(AccountName0,charindex('Domain=',Accountname0)+8,(charindex('Name=',Accountname0)-charindex('Domain=',Accountname0)-10)) as 'Domain Name', substring(AccountName0,len(AccountName0)-charindex('"',reverse(AccountName0),2)+2,charindex('"',reverse(AccountName0),2)-2) as 'User Name'
    from v_GS_SYSTEM
    INNER JOIN v_GS_LocalAdmins ON v_GS_SYSTEM.ResourceID = v_GS_LocalAdmins.ResourceID
    INNER JOIN v_R_System A ON v_GS_SYSTEM.ResourceID=A.ResourceID

    JOIN v_FullCollectionMembership FCM on v_gs_system.ResourceID = FCM.ResourceID
    where (AccountName0 not like '%Administrator%' AND AccountName0 not like '%Domain Admins%')

    and
    FCM.CollectionID='SDG00382'

    Also see; http://social.technet.microsoft.com/Forums/en-CA/configmgrinventory/thread/6e1000cf-283a-4958-b6d4-ec43d82c8376

    Simon Brouillard

    • Marked as answer by DonD1976 Wednesday, July 11, 2012 2:39 PM
    Wednesday, July 11, 2012 2:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you both!.  This one worked Simon!  Tweaked the statements from Eswar and this version seemed to work correctly! 

    BTW, I tried learning and following the steps from the guidelines from this resource:  http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=22052

    but do you guys know of other resources that would be helpful with SQL statements and Cutom reports?

    Again, thank you both for the colloborative efforts!

    Don D.

    Wednesday, July 11, 2012 2:42 PM