I need help on scripting a report for the K1000 that for each machine pulls all Users that are set as Local Administrators on each PC.

Question

I have a script that somewhat does the job, but I need it tweaked a little bit to where it can omit certain entries like "Administrator", "Domain Name\PC-Techs" and "Domain Name\Domain Admins". These are common on each machine and I don't need that information. Any help would be appreciated! Here is a copy of the script that I currently have....

SELECT MACHINE.NAME AS SYSTEM_NAME, (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=6842) AS MACHINE_CUSTOM_INVENTORY_0_6842, (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=6843) AS MACHINE_CUSTOM_INVENTORY_0_6843, MACHINE.IP, OS_NAME FROM MACHINE ORDER BY SYSTEM_NAME, INET_ATON(IP)

Answer

best to use this method nowhttp://www.itninja.com/blog/view/create-a-cir-to-get-a-clean-list-of-your-local-admins-and-then-filter-out-the-it-approved-admins-also-presented-at-dell-world-user-forum-2014-lessions-from-the-field

Answer

just do what I do. I export to a csv and import that to a macro enabled excel spreadsheet. I used the macro record function to create 2 macros, do a search and replace for the unwanted names to nothing. My first macro strips the garbage in my custom data, I did this so I can look for machines that do not have our domain admin groups listed (laptop users have deleted our IT groups). The second macro removes the IT groups/users to see what is left. I then sort the the admins column and delete any empty rows so I am left with the machines with users that are admins. resort by machine name and save the report

I saved that sheet so when I run my weekly report it takes me 2 minutes to take it from the kbox custom inventory string I acquired to readable data in a excel spreadsheet.

I use ShellCommandTextReturn(cmd.exe /c net.exe localgroup administrators) for my custom inventory

it looks like this under the machine:

the raw csv looks like this, I cut and paste that into my saved macro enabled spread sheet.

after the 1st macro

after the 2nd macro

sorted and deleted ok machines, save as another excel name that is non macro enabled.

Comments:

Hello Smal.Tmcc I am interested in doing your method. Are you able to provide me the configured Macro Excel sheet this way i can try it out on my report.

Thanks - cdam 3 years ago

Answer

You can filter this out in your SQL query but it does get messy. You need to string together several replace statements to get rid of each entry that you want to remove, for instance:This gets rid of all of the junk at the beginning:replace(MCI.STR_FIELD_VALUE, 'Alias name Administrators
Comment Administrators have complete and unrestricted access to the computer/domain

Members

-------------------------------------------------------------------------------
Administrator
', "") as Administrators,To also remove the "DOMAIN\Domain Admins" group from the list you need to add another replace statement so it becomes:replace(replace(MCI.STR_FIELD_VALUE, 'Alias name Administrators
Comment Administrators have complete and unrestricted access to the computer/domain

Members

-------------------------------------------------------------------------------
Administrator
', ""), 'Domain\Domain Admins
', "") as AdministratorsNote that you need to use two back slashes in the normal domain\group context since you need to escape it. So for each normal user and group that you want to remove you need to add another replace statement to the mix.

Answer

Local Administrators on Networked Computers

(without have to use a macro to clean up report)

Setup Customer Inventory

This may already be setup on your system.

Under Inventory,Software, Search for Local Administrator

Verify this Custom Inventory Rule is:

ShellCommandTextReturn(net localgroup Administrators)

If not, then set it up as described below:

Inventory, Software, Choose Action, New

Name: Local Administrators

Select Supported Operating System (to select more than one, hold down the CTRL key- those selected will be highlight in blue.)

Custom Inventory Rule

ShellCommandTextReturn(netlocalgroup Administrators)

After all computers are inventoried by this custom inventory, then thetwo sql report below will a give you the information you required.

SQL Report 1: (Detailed Report)

SELECT MACHINE.IP, MACHINE.USER_NAME, MACHINE.NAME AS SYSTEM_NAME, (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=8124) AS MACHINE_CUSTOM_INVENTORY_0_8124 FROM MACHINE WHERE ((( exists (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 8124 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE like CONCAT('%', MACHINE.USER, '%'))) )) ORDER BY INET_ATON(ifnull(IP,0))

SQL Report 2: (User Name only Report)

SELECT MACHINE.IP, MACHINE.USER_NAME, MACHINE.NAME AS SYSTEM_NAME,
date_format(LAST_SYNC, '%m/%d/%y') as Last_Date_Sync,
date_format(MACHINE.MODIFIED, '%m/%d/%y') as Last_Date_Modified

FROM MACHINE WHERE ((( exists (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 8124 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE like CONCAT('%', MACHINE.USER, '%'))) )) ORDER BY INET_ATON(ifnull(IP,0))