Security
Highlighted

Correlate userid to username

Path Finder

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

0 Karma
Highlighted

Re: Correlate userid to username

Path Finder

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

0 Karma
Highlighted

Re: Correlate userid to username

Path Finder

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

0 Karma
Highlighted

Re: Correlate userid to username

Path Finder

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.

0 Karma
Highlighted

Re: Correlate userid to username

Path Finder

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

0 Karma
Highlighted

Re: Correlate userid to username

Path Finder

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.

0 Karma
Highlighted

Re: Correlate userid to username

SplunkTrust
SplunkTrust

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)
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.