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.
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
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
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
good point. somesoni2!. I will correct it
Thanks for helping out! I am having some issues with this. Individually the searches work fine. But not when put together like that.
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
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?
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
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?
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" )
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
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.