• State Suggested Answer
  • Locked Locked
  • Replies 11 replies
  • Answers 1 answer
  • Subscribers 33 subscribers
  • Views 8279 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Custom report - local admin and local group membership of servers

mohammed.shah
over 7 years ago

Hi guys,

I need to produce a report that used to be present in Quest Reporter (which is now end of life) which checks the local admin and local group membership of servers within our organization.

It would need to exclude system based accounts or the authenticated user accounts so it only captures users and groups that have been added.

 

Is this possible at all within Enterprise Reporter? we are running version 2.5.1 build 200.

 

Many thanks,

 

Mo.

Parents
  • 0 over 6 years ago
    Hi Mo

    If you open Report Manager and edit your "Local Administrators" report you'll see a "Query" tab. Go to the very end of the query and add the following lines above "order by ...". Note: You can copy and paste the query into Microsoft SQL Server Management Studio to test things out if that works best for you.

    For "Computer name does not contain value" you can add *one* of the following (whichever works best for you):

    ... This one will exclude all computers that match "ComputerA".
    ... where CP.ComputerName not like '%computera%'

    ... This one will exlude "ComputerA1", "ComputerA2" and "ComputerA3
    ... where CP.ComputerName not in ('computera1', 'computera2', 'computera3')

    For "Members do not end with value" you can add *one* of the following (whichever works best for you, but note this only operates on the account name and not the account domain/computer name):

    ... This one will exclude all accounts that end with "Administrator".
    ... where MA.SAMAccountName not like '%administrator'

    ... This one will exlude accounts "Administrator" and "Domain Admins"
    ... where MA.SAMAccountName not in ('administrator', 'domain admins')

    Examples (note how you only need one "where" - other condtions can be joined together by "and"):

    1.
    where CP.ComputerName not like '%computera%'
    and MA.SAMAccountName not in ('administrator', 'domain admins')
    order by CP.DomainName, CP.ComputerName, GroupName, AccountName, Path, Level

    2.
    where CP.ComputerName not in ('computera1', 'computera2', 'computera3')
    and MA.SAMAccountName not like '%administrator'
    order by CP.DomainName, CP.ComputerName, GroupName, AccountName, Path, Level

    Hope this is what you need.

    Thanks
    Adam
Reply
  • 0 over 6 years ago
    Hi Mo

    If you open Report Manager and edit your "Local Administrators" report you'll see a "Query" tab. Go to the very end of the query and add the following lines above "order by ...". Note: You can copy and paste the query into Microsoft SQL Server Management Studio to test things out if that works best for you.

    For "Computer name does not contain value" you can add *one* of the following (whichever works best for you):

    ... This one will exclude all computers that match "ComputerA".
    ... where CP.ComputerName not like '%computera%'

    ... This one will exlude "ComputerA1", "ComputerA2" and "ComputerA3
    ... where CP.ComputerName not in ('computera1', 'computera2', 'computera3')

    For "Members do not end with value" you can add *one* of the following (whichever works best for you, but note this only operates on the account name and not the account domain/computer name):

    ... This one will exclude all accounts that end with "Administrator".
    ... where MA.SAMAccountName not like '%administrator'

    ... This one will exlude accounts "Administrator" and "Domain Admins"
    ... where MA.SAMAccountName not in ('administrator', 'domain admins')

    Examples (note how you only need one "where" - other condtions can be joined together by "and"):

    1.
    where CP.ComputerName not like '%computera%'
    and MA.SAMAccountName not in ('administrator', 'domain admins')
    order by CP.DomainName, CP.ComputerName, GroupName, AccountName, Path, Level

    2.
    where CP.ComputerName not in ('computera1', 'computera2', 'computera3')
    and MA.SAMAccountName not like '%administrator'
    order by CP.DomainName, CP.ComputerName, GroupName, AccountName, Path, Level

    Hope this is what you need.

    Thanks
    Adam
Children
No Data