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)

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

Answers

0
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.


Answered 06/11/2014 by: SMal.tmcc
Red Belt

Please log in to comment
0
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<br/>Comment        Administrators have complete and unrestricted access to the computer/domain<br/><br/>Members<br/><br/>-------------------------------------------------------------------------------<br/>Administrator<br/>', "") 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<br/>Comment        Administrators have complete and unrestricted access to the computer/domain<br/><br/>Members<br/><br/>-------------------------------------------------------------------------------<br/>Administrator<br/>', ""), 'Domain\\Domain Admins<br/>', "") as Administrators

Note 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.
Answered 06/12/2014 by: chucksteel
Red Belt

Please log in to comment
This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

0

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))

Answered 02/24/2016 by: mtcooper
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Share SNMP OID and MIB Information
It’s easy to add custom SNMP OID GETs to your Dell KACE K1000 inventory, but where do you find the exact OIDs you need for a specific device? This is the place to share the OIDs and MIBs you have – and to request the ones you need.

Share