How to find duplicate windows user names

[ShaunBaker]

Hello all, I'm trying to think of a way to search for instances where a duplicate user account/name has been generated by an admin, so say user john123 was made on Monday, then another user john123 was made a month later. This is Splunk Enterprise 6.4 using the active directory plugin. We currently make a lookup table of user names, so I suppose step one would be to call on that table, but what would the string be to ask to show duplicates, or duplicate user names with different SIDs etc?

[adonio · Accepted solution]

hello ShaunBaker,
here is a suggestion for solution without your lookup, if you ask, how can i know all the users already active? an example code based first solution is all the way down.
if AD data is parsed right using the add-on, you can search for EventCode=4720 user account was created described here:
https://www.ultimatewindowssecurity.com/securitylog/encyclopedia/event.aspx?eventID=4720
fields supposed to be extracted already.
from the top of my head there is src_user field which stands for who created the account and user field for the name of account that was created.
i believe an account is created once per user name and therefore a specific event is generated for each account.
search ... | stats count by user | where count >1
that will give you duplicates.
plenty of it is already built in the Splunk app for windows infrastructure
Assuming you are still going with the lookup route, I assumed there is a field called user in the lookup, if not, either change the field name or change the search:

index = <index> sourcetype=<sourcetpye>  EventCode= 4720 user=*  
| dedup user 
| eval active_user="T"
| append [ inputlookup <yourLookupFile.csv | eval active_user="F" ]
| stats count(eval(active_user=="F")) as active by user
| where active==1

this will give you a list of new accounts (in the time period you searched) that do not appear in the lookup
this solution relies on one by @lguinn

hope it helps

[DalJeanis]

Assuming your lookup table is called mylookupname, and the fields are username and SID...

| inputlookup mylookupname | stats count username values(SID) as SID | where mvcount(SID)>1

[ShaunBaker]

The table did not have SID and ldap attributes does not seem to support it, but to test I tried-

inputlookup -mytablenamehere- | stats count DisplayName values(sAMAccountName) as sAMAccountName | where mvcount (sAMAccountName)>1

and get "Error in 'stats' command. The argument 'DisplayName' is invalid. But DisplayName is in the lookup table csv. When I use

inputlookup -mytablenamehere- | stats count values(sAMAccountName) as sAMAccountName | where mvcount (sAMAccountName)>1

I don't get any errors, but when I change it to <1, or =1 I still get no results (figure =1 would give me all the users found in the lookup).

[ShaunBaker]

Awesome, I'll try it out, pretty sure there is a SID field, or can go off of the samaccountname that I know for sure has a field.