topic Re: tstats subsearch in Splunk Search

tstats subsearch

griggsy — Mon, 02 Jul 2018 11:43:01 GMT

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!

Re: tstats subsearch

srichansen — Mon, 02 Jul 2018 12:28:24 GMT

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

Re: tstats subsearch

srichansen — Mon, 02 Jul 2018 12:30:02 GMT

ps. you will need to specify an earliest, latest in the sub search if the join data is in another time frame.

Re: tstats subsearch

griggsy — Tue, 03 Jul 2018 09:36:01 GMT

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!

Re: tstats subsearch

srichansen — Tue, 03 Jul 2018 09:44:48 GMT

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.

Re: tstats subsearch

griggsy — Tue, 03 Jul 2018 10:05:08 GMT

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

Re: tstats subsearch

srichansen — Tue, 03 Jul 2018 10:21:24 GMT

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?

Re: tstats subsearch

griggsy — Tue, 03 Jul 2018 10:46:37 GMT

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!

Re: tstats subsearch

srichansen — Tue, 03 Jul 2018 11:35:11 GMT

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]

Re: tstats subsearch

griggsy — Tue, 03 Jul 2018 11:43:44 GMT

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!

Re: tstats subsearch

srichansen — Tue, 03 Jul 2018 11:53:00 GMT

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.

Re: tstats subsearch

griggsy — Tue, 03 Jul 2018 13:25:11 GMT

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!

Re: tstats subsearch

srichansen — Wed, 04 Jul 2018 07:41:11 GMT

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

Re: tstats subsearch

griggsy — Wed, 04 Jul 2018 09:15:05 GMT

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.

Re: tstats subsearch

srichansen — Thu, 05 Jul 2018 08:00:41 GMT

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?