topic Re: Correlate userid to username in Security

Correlate userid to username

matthew_foos — Thu, 12 Jul 2018 15:44:56 GMT

Splunkers,

The following search returns user lockouts:

index=winevents EventCode=4740

I have a user field which is "abc1234".

The following search returns a target_user field "First Last (userid)"

index=msad sourcetype=ActiveDirectory
| eval target_user = givenName." ".sn." (".sAMAccountName.")"

How can I combine these searches to produce a field that would let me display:

First Last (userid) and Account Lockouts in a single table?

Thanks

Re: Correlate userid to username

jodyfsu — Thu, 12 Jul 2018 16:22:13 GMT

Do you have some example events we can look at? You may need to do a join.

Re: Correlate userid to username

matthew_foos — Thu, 12 Jul 2018 16:36:28 GMT

Lockout event: - I've taken out the irrelevant data

07/12/2018 11:16:16 AM
LogName=Security
SourceName=Microsoft Windows security auditing.
EventCode=4740
Message=A user account was locked out.

Account That Was Locked Out:
Account Name: abc1234

MSAD Event - I've taken out the irrelevant data

07/12/2018 11:31:04.229
Names:
name=kfp1234
displayName=Kenny Powers
distinguishedName=CN=kfp1234
givenName=Kenny
sn=Powers
cn=kfp1234
Object Details:
sAMAccountName=kfp1234

Re: Correlate userid to username

jodyfsu — Thu, 12 Jul 2018 16:41:18 GMT

Thanks. So, in the Lockout Event, the Account Name: abc1234, will that match to either the name, cn, or sAMAccountName?

If yes, you can do a join and then a sub query.

Re: Correlate userid to username

matthew_foos — Thu, 12 Jul 2018 16:44:41 GMT

It appears that way. Could you help me with the SPL?

Re: Correlate userid to username

jodyfsu — Thu, 12 Jul 2018 16:55:47 GMT

See if this will work

index=winevents EventCode=4740
| rename "Account Name" as cn
| join cn
    [search index=msad sourcetype=ActiveDirectory]
| table _time givenName sn cn Message

I'm renaming Account Name to cn so the join will work.

Re: Correlate userid to username

DalJeanis — Thu, 12 Jul 2018 18:06:16 GMT

Pseudocode for that is as follows:

(your search that gets the lockout events)
OR
(your search that gets the MSAD events)
| fields ... list all (and ONLY) the fields you need from either type of record...

| rename COMMENT as "set up a single named key to match the records, and create the target_user data" 
| eval cn=concatenate(cn,"Account Name")
| eval target_user = givenName." ".sn." (".sAMAccountName.")"

| rename COMMENT as "roll the target_user data from the MSAD to the lockout records, then drop the MSAD records" 
| eventstats values(target_user) as target_user by cn
| where (the record is a lockout record)