Splunk Search

Using the results from one search as a field to use in another search

DeanDeleon0
Path Finder

Hello all,

I am trying to combine two different searches to correlate with one another.

The first search is:

EventCode=4738 Account_Expires!="-" | table _time, Account_Name, Account_Expires| eval  Account_Name=mvindex(Account_Name, -1) 

This will provide me any AD account that had made changes to the account expiry. I use the | eval Account_Name=mvindex(Account_Name, -1) to show me the 2nd name as the first one is the person who made the change. The second one is the account was changed.

Next we have an OU in AD that we move the user to called Users - Disabled.

EventCode=5139  New_DN="CN=*,OU=Users - Disabled,DC=testdomain,DC=ca" | table _time, Account_Name, New_DN, Old_DN

This is so I can see if a user was moved to this "Users - Disabled" OU

How do I combine these by obtaining the "Account_Name" from the first search to use as an insert to search for this user that was moved(as per below)?

EventCode=5139  New_DN="CN=<Insert "Account_Name" here>,OU=Users - Disabled,DC=testdomain,DC=ca" | table _time, Account_Name, New_DN, Old_DN

The problem I have is that EventCode=5139 does not show the second user as EventCode=4738 does. The goal is here is to see how much time has transpired from when an "Account_Expires" was set and when the account is moved to this "Users - Disabled" OU. Is this something that is possible?

After that I would want to set an alert to notify me when the "Account_Expires" field was changed and if the AD account was not moved to the "Users - Disabled" OU within about 10 days. Any suggestions would be appreciated.

0 Karma
1 Solution

MonkeyK
Builder

Dean, should be something like this:

EventCode=5139  [|search EventCode=4738 Account_Expires!="-" | eval  Account_Name=mvindex(Account_Name, -1) |eval New_DN="\"CN=".Account_Name.",OU=Users - Disabled,DC=testdomain,DC=ca\"" | table New_DN] 
| table _time, Account_Name, New_DN, Old_DN

Basically, that just uses you first search to create the New_DN field for the second search

View solution in original post

0 Karma

MonkeyK
Builder

Dean, should be something like this:

EventCode=5139  [|search EventCode=4738 Account_Expires!="-" | eval  Account_Name=mvindex(Account_Name, -1) |eval New_DN="\"CN=".Account_Name.",OU=Users - Disabled,DC=testdomain,DC=ca\"" | table New_DN] 
| table _time, Account_Name, New_DN, Old_DN

Basically, that just uses you first search to create the New_DN field for the second search

0 Karma

somesoni2
SplunkTrust
SplunkTrust

I would add | table New_DN at the end of subsearch. Else it would return all available fields from subsearch and will not work.

EventCode=5139  [|search EventCode=4738 Account_Expires!="-" | eval  Account_Name=mvindex(Account_Name, -1) |eval New_DN="\"CN=".Account_Name.",OU=Users - Disabled,DC=testdomain,DC=ca\"" | table New_DN ] 
 | table _time, Account_Name, New_DN, Old_DN
0 Karma

MonkeyK
Builder

good point. somesoni2!. I will correct it

0 Karma

DeanDeleon0
Path Finder

Thanks for helping out! I am having some issues with this. Individually the searches work fine. But not when put together like that.

0 Karma

MonkeyK
Builder

Dean,
we might need to debug this a little. What happens when you do just the first (subquery) part?

search EventCode=4738 Account_Expires!="-" | eval Account_Name=mvindex(Account_Name, -1) |eval New_DN="\"CN=".Account_Name.",OU=Users - Disabled,DC=testdomain,DC=ca\"" | table New_DN

And if it looks like a fqdn, what happens if you manually stick that values into the outer query?
EventCode=5139 New_DN=...
| table _time, Account_Name, New_DN, Old_DN

0 Karma

DeanDeleon0
Path Finder
EventCode=4738 Account_Expires!="-" | eval Account_Name=mvindex(Account_Name, -1) |eval New_DN="\"CN=".Account_Name.",OU=Users - Disabled,DC=testdomain,DC=ca\"" | table New_DN

Will result in:

"CN=bruce.wayne,OU=Users - Disabled,DC=testdomain,DC=ca"

EventCode=5139 New_DN="CN=bruce.wayne,OU=Users - Disabled,DC=testdomain,DC=ca" | table _time, Account_Name, New_DN

Yields no results. However, I see where the problem is. EventCode 5139 displays the New_DN account name with a space:

CN=**Bruce Wayne**,OU=Users - Disabled,DC=testdomain,DC=ca

This copies the account name with the period between the first and last name.

"CN=**bruce.wayne**,OU=Users - Disabled,DC=testdomain,DC=ca"

Is there a simple way to create the space and remove the period?

0 Karma

MonkeyK
Builder

Good catch on the space!

to replace the period with a space, use this eval statement:

replace(Account_Name,"\."," ")

so now the whole thing would be

EventCode=5139  [|search EventCode=4738 Account_Expires!="-" | eval  Account_Name=mvindex(Account_Name, -1) |eval New_DN="\"CN=".replace(Account_Name,"\."," ").",OU=Users - Disabled,DC=testdomain,DC=ca\"" | table New_DN] 
 | table _time, Account_Name, New_DN, Old_DN
0 Karma

DeanDeleon0
Path Finder

I believe we are just one step away now. I think it is here from when you asked me to test them individually:

   EventCode=5139 New_DN=...
    | table _time, Account_Name, New_DN, Old_DN

I think it ends up looking like this instead, where the "New_DN=" is not inserted so it does not yield any results:

EventCode=5139 "CN=bruce wayne,OU=Users - Disabled,DC=testdomain,DC=ca"  | table _time, Account_Name, New_DN, Old_DN 

How do we insert "New_DN=" in front of that?

0 Karma

MonkeyK
Builder

When you use the subsearch's table command should assign the field name, so

[|search EventCode=4738 Account_Expires!="-" | eval Account_Name=mvindex(Account_Name, -1) |eval New_DN="\"CN=".replace(Account_Name,"."," ").",OU=Users - Disabled,DC=testdomain,DC=ca\"" | table New_DN]

should result resolve to
New_DN="CN=bruce wayne,OU=Users - Disabled,DC=testdomain,DC=ca"

You can see this has taken place after the run if you click on "Job|inspect Job"
and then "search log" in the dialog that appears.
You will be able to search for "expanded index search" (it may be a few results down) or "New_DN="
and you will see EventCode=5139 (New_DN="CN=bruce wayne,OU=Users - Disabled,DC=testdomain,DC=ca")

if there are multiple New_DNs, you will see them OR'd:
EventCode=5139 (New_DN="CN=bruce wayne,OU=Users - Disabled,DC=testdomain,DC=ca" OR New_DN="CN=clark kent,OU=Users - Disabled,DC=testdomain,DC=ca" )

0 Karma

DeanDeleon0
Path Finder

Thanks! This is working! Much appreciated! The issue was there was a subfolder in the OU=Users - Disabled. example: OU=December,OU=Users - Disabled,...etc

0 Karma

MonkeyK
Builder

ah! Glad it works for you.
FQDNs always mess me up.

As long as you are building the DN anyway, you can use a wildcard. I probably should have considered that.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...