Let me tell you what I am trying to accomplish instead of complicating the issue...

I need to be able to see the DOMAIN users with LOCAL ADMINISTRATOR rights to the machines in my environment. By leveraging KACE I can return that info, but the format leaves a lot to be desired...

Using Custom Inventory with "ShellCommandTextReturn(cmd.exe /c net localgroup administrators)"

2) Local Admin Group Users (CIF):Alias name administrators
Comment Administrators have complete and unrestricted access to the computer/domain

Members

-------------------------------------------------------------------------------
Administrator
DOMAIN-\BAPM_NAS_INF
DOMAIN-\BART_OU_Administrators
DOMAIN-\Domain Admins
DOMAIN\SSDFNG
DOMAIN-\SYYEFECC
Guest
sycghs5gdng
SYJfsDGAd5sdaEA
The command completed successfully.


This is the table format I need to get to(leveraging CSV and Excel):

__________________________________
Server  | Local Admin Account Name
ServerA | DOMAIN\USER_____________
ServerA | DOMAIN\USER2____________
ServerB | DOMAIN\USER1 ___________
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • No idea on this one but congrats on your Black Belt!
Please log in to comment

Community Chosen Answer

4

We use kace and I created a custom inventory rule that gets the info from the machines using net.exe

Then I use the following sql report and export to csv.

SELECT (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=22271) AS MACHINE_CUSTOM_INVENTORY_0_22271, MACHINE.NAME AS SYSTEM_NAME  FROM MACHINE   WHERE ((1  in (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 22271 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE is not null)) )  ORDER BY MACHINE_CUSTOM_INVENTORY_0_22271, MACHINE_CUSTOM_INVENTORY_0_22271

I open that export in excell to run a macro that does a bunch of search and replaces (with nothing) to filter the garbage text out and the known domain users out.

 

Answered 10/10/2013 by: SMal.tmcc
Red Belt

  • SMal when i ran the report i have no machine listed. I believe its because i dont know the ID of the custom inventory report. How do i find that? CUSTOM_INVENTORY.SOFTWARE_ID=*****
    • The easiest method is to login to your KBOX with the /adminui URL instead of just /admin. Then go to Inventory, Software and open the software title for the custom inventory rule. The ID will be at the end of the URL.
      • also in the lower left corener of the browser window when you point mouse at item in software window
    • you can also use the report creator to do this for you
  • I was able to find the Software ID but when i run the report there is nothing in the report. Belwo is the report i copied from above and enter the software ID of thecustom inventory report i created just has above


    SELECT (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=79202) AS MACHINE_CUSTOM_INVENTORY_0_79202, MACHINE.NAME AS SYSTEM_NAME FROM MACHINE WHERE ((1 in (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 79202 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE is not null)) ) ORDER BY MACHINE_CUSTOM_INVENTORY_0_79202, MACHINE_CUSTOM_INVENTORY_0_79202
    • when you look at your custom software item you created are there any machines listed at the bottom of the page yet?
      • Hi Smal,
        I am also trying to create this report. I am stuck right at this point. I do not see machines at the bottom of the software item, therefore when I run the report it is empty. I used the wizard to create the report, and verified that the ID from the link is the same that is in the SQL report. Is there something else that I am missing?

        Any help would be appreciated.
      • Apparently the syntax is different on 5.5. This is what I ended up having to use in order for it to work: ShellCommandTextReturn(cmd.exe /c powershell net.exe localgroup administrators)

        thanks
  • here is the search and replace cleanup macro's I run, I found this much easier then messing with sql.

    macro 1:
    Sub Cleanup()
    '
    ' Cleanup Macro
    ' cleans up extra text 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
Please log in to comment

Answers

3

Answered 10/16/2013 by: SMal.tmcc
Red Belt

Please log in to comment
2

The closest I can get is the following query:

 SELECT MACHINE.NAME, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(STR_FIELD_VALUE, "-------------------------------------------------------------------------------<br/>", -1), "<br/>The command completed successfully.", 1), "<br/>", ",") AS ADMINS FROM ORG1.MACHINE_CUSTOM_INVENTORY JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID WHERE SOFTWARE_ID = 22066 AND LOCATE("-------------------------------------------------------------------------------", STR_FIELD_VALUE) > 0

 

Note that you will need to change the SOFWARE_ID = 22066 to match the SOFTWARE_ID of your custom inventory rule that pulls in the administrators. This generates are report showing:

MACHINE1 | Admin1,Admin2,Admin3
MACHINE2 | Admin1,Admin4

MySQL doesn't include a method to break a string character into multiple rows so you would need some sort of programming to accomplish that part. It might be possible to do that part in Excel, but I'm not positive.

Answered 10/10/2013 by: chucksteel
Red Belt

Please log in to comment
0
To get all users who are local administrators:
ShellCommandTextReturn(cmd /q /c for /f "tokens=* skip=6" %a in ('net localgroup administrators ^| find /V "The command completed successfully."') do echo %a)

To get only the domain users who are local administrators:
ShellCommandTextReturn(cmd /q /c for /f "tokens=* skip=6" %a in ('net localgroup administrators ^| find /V "The command completed successfully."') do echo %a | find /I "DOMAIN")

To get non-domain users who are local administrators:
ShellCommandTextReturn(cmd /q /c for /f "tokens=* skip=6" %a in ('net localgroup administrators ^| find /V "The command completed successfully."') do echo %a | find /I /V "DOMAIN")

Answered 08/17/2014 by: flip1001
Fourth Degree Green Belt

  • The code formatting messed up, the 3 code blocks are:

    1. To get all users who are local administrators

    2. To get only the domain users who are local administrators

    3. To get non-domain users who are local administrators
Please log in to comment
Answer this question or Comment on this question for clarity