topic Re: Using the results from one search as a field to use in another search in Splunk Search

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

DeanDeleon0 — Tue, 29 Sep 2020 16:58:20 GMT

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.

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

MonkeyK — Tue, 28 Nov 2017 22:08:44 GMT

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

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

somesoni2 — Tue, 28 Nov 2017 22:41:18 GMT

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

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

MonkeyK — Wed, 29 Nov 2017 01:00:07 GMT

good point. somesoni2!. I will correct it

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

DeanDeleon0 — Wed, 29 Nov 2017 17:02:33 GMT

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

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

MonkeyK — Tue, 29 Sep 2020 16:59:01 GMT

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

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

DeanDeleon0 — Wed, 29 Nov 2017 21:51:17 GMT

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?

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

MonkeyK — Thu, 30 Nov 2017 01:26:12 GMT

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

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

DeanDeleon0 — Thu, 30 Nov 2017 16:24:51 GMT

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?

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

MonkeyK — Tue, 29 Sep 2020 16:59:52 GMT

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

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

DeanDeleon0 — Mon, 04 Dec 2017 16:26:38 GMT

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

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

MonkeyK — Tue, 05 Dec 2017 12:24:18 GMT

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.