Help with tstats SPL query

[SabariRajanT]

Hi Team,

In below query I am trying to pull all the host from various index and match those host in a list lookup file(list123) and keep that in a table and retuns values with host fields and their respective field value available in the column "vore_or_yroe" in the list123 file.

I used below query, I can pull only host values in a table but not vore_or_yroe. (vore_or_yroe) it's not returning exact values from the list123 lookup file

| tstats latest(_time) as latest where (index=*_nmap OR index=*_net OR index=*_ds OR index=*_other OR index=*_cld) earliest=-1h by host
| search
[| inputlookup list123.csv
| search vore_or_yroe="*"
| search vrit_cpco="try"
| rename trit_host AS host
| table host vore_or_yroe ]
| lookup list123.csv trit_host AS host OUTPUT crit_opco
| eval OPCO=upper(vrit_cpco)
| table host vore_or_yroe
| sort host
| outputlookup rtun1_clone.csv

[PickleRick]

Firstly, don't use constructions like:

<something> | search <some_condition>

If the condition can be a part of the <something>. Yes, Splunk can often optimize the search and append the condition to the preceeding command but I suppose it's not always that it's able to do so and also let's not get into a habit of writing bad searches.

So your subsearch-generated set of conditions should be a part of the initial tstats command.

| tstats latest(_time) as latest where (index=*_nmap OR index=*_net OR index=*_ds OR index=*_other OR index=*_cld) earliest=-1h 
[| inputlookup list123.csv
| search vore_or_yroe="*" vrit_cpco="try"
| rename trit_host AS host
| table host vore_or_yroe ]
by host

 BTW, latest(_time) is a tricky usage of latest(). In case of the _time field it will be OK, but if you do latest() on other timestamp field, you could get something different than you wished for.

And what do you mean by "it's not returning exact values from the lookup"? You apply the lookup and only want a single field as output so other fields are not getting returned. You asked for it.

From your search I see that your lookup must at least have fields:

- vore_or_yroe
- vrit_cpco
- trit_host
- crit_opco

Your lookup only matches on the host field from the tstats output to the trit_host in the lookup file and outputs just crit_opco field from the lookup.

If you want to return more fields from the lookup either don't specify the OUTPUT clause for the lookup command (but be wary of duplicate-named fields) or OUTPUT a specific list of fields you want to get as a result.

[tscroggins]

Hi,

Try this:

| tstats latest(_time) as latest where (index=*_nmap OR index=*_net OR index=*_ds OR index=*_other OR index=*_cld) earliest=-1h by host
| lookup list123.csv trit_host as host output vore_or_yroe vrit_cpco
| search vore_or_yroe=* vrit_cpco=try
| table host vore_or_yroe
| sort host
| outputlookup rtun1_clone.csv

[SabariRajanT]

hi @tscroggins :

we don't see all the data being pulled from list123.csv. There is a disperancy in data match from the search index and match with lookup file.

Please help with other alternative query.

Thanks,

Sabari

[tscroggins]

Hi @SabariRajanT,

If you can provide samples of your events and a sample of list123.csv, I can help with alternatives.