KACE Product Support

http://www.itninja.com/question/i-need-to-list-all-accounts-with-local-admin-right-to-a-machine

with the report wizard

I found instead of trying to create a sql report that is clean I went to exporting to CSV and bringing into excel and running two macro's that do the cleanup and one other to data sort.

here are the cleanup macros

macro 1:
Sub Cleanup()
'
' Cleanup Macro
' cleans up extra test from export
'
' Keyboard Shortcut: Ctrl+c
'
    Cells.Replace What:= _
        "Alias name     administrators\nComment        Administrators have complete and unrestricted access to the computer/domain\n\nMembers\n\n-------------------------------------------------------------------------------\n\n" _
        , Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
        False, SearchFormat:=False, ReplaceFormat:=False
    Range("B8").Select
    ActiveCell.FormulaR1C1 = _
        "Alias name     administrators\nComment        Administrators have complete and unrestricted access to the computer/domain\n\nMembers\n\n-------------------------------------------------------------------------------\n0\nAdministrator\ndfault\nTMCCADMN\Desktop Local Admins\nTMCCADMN\Domain Admins\nTMCCADMN\ITO PC Admins\nThe command completed successfully.\n\n"
    Cells.Replace What:= _
        "Alias name     administrators\nComment        Administrators have complete and unrestricted access to the computer/domain\n\nMembers\n\n-------------------------------------------------------------------------------" _
        , Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
        False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="\nThe command completed successfully.", Replacement:= _
        "", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="\n\n", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="\n", Replacement:=" username ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

macro 2:
Sub names()
'
' names Macro
' get rid of known names and groups
'
' Keyboard Shortcut: Ctrl+n
'
    Cells.Replace What:="username backup", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="username Administrator", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="username dfault", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="username TMCCADMN\Desktop Local Admins", Replacement:= _
        "", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="username TMCCADMN\Domain Admins", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Cells.Replace What:="username TMCCADMN\ITO PC Admins", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
End Sub

Then I get a spreadsheet that looks like this:

The sql code I posted in the other question is just the wizard build selected to view as sql

the above wizard build should give you a report in html that looks like this:

looking at this I see 3 local users (Administrator, backup, homer),2 domain users (tmccadmn\homer and smal), and 3 domain groups (tmccadmn\desktop local admins, domain admins, ito pc admins).