Hi,
I have a tstats query working perfectly however I need to then cross reference a field returned with the data held in another index. Example query which I have shortened
| tstats summariesonly=t count FROM datamodel=Datamodel.Name WHERE earliest=@d latest=now datamodel.EventName="LOGIN_FAILED" by datamodel.EventName, datamodel.UserName
What I am after doing is then running some kind of subsearch to query another index to return more information about the user. I thought of doing something like
| tstats summariesonly=t count FROM datamodel=Datamodel.Name WHERE earliest=@d latest=now datamodel.EventName="LOGIN_FAILED" by datamodel.EventName, datamodel.UserName | [search index=ad Name=datamodel.UserName]
However it doesn't seem to like it. Can someone point me in the correct direction I am banging my head on the wall!
Thanks!
Hi,
there are a few ways to do this.
The first is to use a lookup with helps if you have more than 50k events that you want to link without hitting any limits. In this case you would need to use | outputlookup to create the csv with a search.
otherwise you can use a join as so:
| tstats summariesonly=t count FROM datamodel=Datamodel.Name WHERE earliest=@d latest=now
datamodel.EventName="LOGIN_FAILED" by datamodel.EventName, datamodel.UserName
| join type=left datamodel.UserName
[search index=ad
| rename Name=datamodel.UserName | table datamodel.UserName, (+ fields you want to return) ]
A join might not be the most efficient way to do this if you are linking lots of records. If so it would be better to call up all the events from both indexes in the tstats command. This can be tricky using tstats though.
Hope that helps,
regards
Simon
ps. you will need to specify an earliest, latest in the sub search if the join data is in another time frame.
Hi Simon,
Thanks for the help.
Here is where I am at:
| tstats summariesonly=t count FROM datamodel=Datamodel.Name WHERE earliest=@d latest=now
datamodel.EventName="LOGIN_FAILED" by datamodel.EventName, datamodel.UserName
| rename datamodel.* as *
| join type=left UserNameSplit [ search index=ad SamAccountName=UserNameSplit | table lastLogon]
But I don't appear to get anything being returned. the field lastLogon is being created in the table view but is just blank. Pulling my hair our with it now!
are you trying to filter your search to where field SamAccountName=UserNameSplit or are you trying to rename it? if you want to rename it you will need to add "| rename SamAccountName as UserNameSplit":
further i think you need to have the join field in your table results as below:
| tstats summariesonly=t count FROM datamodel=Datamodel.Name WHERE earliest=@d latest=now
datamodel.EventName="LOGIN_FAILED" by datamodel.EventName, datamodel.UserName
| rename datamodel.* as *
| join type=left UserNameSplit [ search index=ad | rename SamAccountName as UserNameSplit | table UserNameSplit, lastLogon]
hopefully that will give results.
Hi Simon.
UserNameSplit is an eval field to drop the domain name etc from the value. I want to search for SamAccountName where the value is UserNameSplit if that makes sense?
So instead of SamAccountName="JoeBloggs" UserNameSplit would = "JoeBloggs" and we would use UserNameSplit to do the search?
I need coffee
Ok, so join works by having two fields with the same field name and values.
If your field with users is the following:
tstats search its "UserNameSplit" and
sub search its "SamAccountName"
you will need to rename one of them to match the other.
in my example I renamed the sub search field with "| rename SamAccountName as UserNameSplit"
the part of the join statement "| join type=left UserNameSplit" tells splunk on which field to link. As long as you have renamed the fields and the values are the same it should return values.
You can check if anything is linking by removing the "type=left" from the join. This will make the join only return matched record as apposed to all tstats records.
If you are still not seeing anything then it might be that your sub search time frame is not right. In you tstats you are searching "earliest=@d latest=now". Is the data in you sub search for the same period?
Hi Simon,
Thanks again for the help.
I have updated the eval command to be SamAccountName instead of UserNameSplit so the last line of the search is:
| eval SameAccountName=mvindex(split(UserName,"\\"),-1) | join type-left SamAccountName [search index=ad SamAccountName earliest=@d latest=now | table SamAccountName, lastLogon]
and just don't appear to be pulling any data in from the AD index. I know the data is there and field names are correct as can run a normal search on the index with one of the username as:
index=ad SamAccountName="JoeBloggs" | table SamAccountName, lastLogon
Sorry to be a pain!
it is probably your sub search time period.
when you get a result using
index=ad SamAccountName="JoeBloggs" | table SamAccountName, lastLogon
what is the event time? Is it the within the day periode and does it give the same result as
index=ad SamAccountName="JoeBloggs" earliest=@d latest=now | table SamAccountName, lastLogon
Do you get results if you change it to the following:
| tstats summariesonly=t count FROM datamodel=Datamodel.Name WHERE earliest=@d latest=now
datamodel.EventName="LOGIN_FAILED" by datamodel.EventName, datamodel.UserName
| rename datamodel.* as *
| eval SameAccountName=mvindex(split(UserName,"\\"),-1)
| join type=left SamAccountName
[search index=ad earliest=-1 latest=now | stats latest(lastLogon) as lastLogon by SamAccountName]
Hi Simon,
I have tried with the time range in the AD search straight from the index and get the same result.
However still nothing on the tstats query with the above changes - thanks for all your help by the way!
I tried to clean it up a bit and found a type-o in the field names. Does this work?
| tstats summariesonly=t count FROM datamodel=Datamodel.Name WHERE earliest=@d latest=now AND datamodel.EventName="LOGIN_FAILED" by datamodel.UserName
| eval SameAccountName=mvindex(split(datamodel.UserName,"\\"),-1)
| join type=left SameAccountName
[ search index=ad earliest=-1 latest=now
| stats latest(lastLogon) as lastLogon by SameAccountName]
Glad to help, hopefully we can get it working.
Hi Simon,
Unfortunately not - the field is called SamAccountName (it's an AD field) 😞
I now have it running and returning fields if I manually set the eval field as eval SamAccountName=JoeBloggs
So the last line of query looks like:
|eval SamAccountName="JoeBloggs" | join type=left SamAccountName [ search index=ad SamAccountName="JoeBloggs" ]
If I put | table lastLogon or any other fields I don't get the data back from the index.
So my problem at moment is getting the search to set SamAccountName to be SamAccountName and then just returning certain fields. If I have to return them all its not end of world but want it to be clean really.
I have tried using the field selector and this also stops it returning data. Cheers again!
That is strange.
The only think i can think of is that the format of the user names is not the same.
I would suggest running
| tstats summariesonly=t count FROM datamodel=Datamodel.Name WHERE earliest=@d latest=now AND datamodel.EventName="LOGIN_FAILED" by datamodel.UserName
| eval SamAccountName=mvindex(split(datamodel.UserName,"\\"),-1)
then copying a name out the SamAccountName field and pasting it in the following to see if you get a result.
index=tfl_ad SamAccountName="*pasteduser*" | stats latest(lastLogon) as lastLogon by SamAccountName
Hi Simon,
Yep when pasting the eval field value it returns values! It just doesn't appear to be setting the value to be the value of the field if that makes sense.
Pete.
That is strange, there must be something that is causeing it to fail in the syntax
Is it possible to paste the exact search you are running?