I need to list all accounts with local admin right to a machine
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 ___________
1 Comment
[ + ] Show comment
-
No idea on this one but congrats on your Black Belt! - petelanglois 11 years ago
Answers (4)
Answer Summary:
Please log in to answer
Posted by:
SMal.tmcc
11 years ago
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.
Comments:
-
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=***** - CEads 11 years ago
-
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. - chucksteel 11 years ago
-
also in the lower left corener of the browser window when you point mouse at item in software window - SMal.tmcc 11 years ago
-
you can also use the report creator to do this for you - SMal.tmcc 11 years ago
-
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 - CEads 11 years ago-
when you look at your custom software item you created are there any machines listed at the bottom of the page yet? - SMal.tmcc 11 years ago
-
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. - Kevino2010 11 years ago -
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 - Kevino2010 11 years ago
-
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 - SMal.tmcc 11 years ago
Posted by:
chucksteel
11 years ago
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.
Posted by:
flip1001
10 years ago
To get all users who are local administrators:
To get only the domain users who are local administrators:
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)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")Comments:
-
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 - flip1001 10 years ago
