topic Re: Join truncating the result. how to avoid it? in Splunk Search

Join truncating the result. how to avoid it?

sp1711 — Mon, 28 Sep 2020 20:11:10 GMT

I have the following query,

I am expecting a table that would have top 3 values for each client ,

client FIELDNAME _time count

This search is truncating a lot of results and I'm not able to get stats for more than a day or two.I tried,

This does not work.

How do I optimize the join so that I can get stats for a longer period? any idea?

P.S I apologize for the formatting. It eats up the query if I try doing it.

Thanks in advance

Re: Join truncating the result. how to avoid it?

woodcock — Fri, 05 Jun 2015 17:37:35 GMT

Try this; it is vastly less efficient but distributes the subsearch limit out by dividing the results over the number of correlation_id values:

 index="index" tag=tag1 sourcetype=access_combined "def"|rex "(?i)/user/(?P<FIELDNAME>[^/]+)"| bucket span=1d _time |stats count by FIELDNAME _time|where count<30 AND count>20 |fields correlation_id | map search="search correlation_id=$correlation_id$ index=\"index\" tag=tag2 sourcetype=access_combined \"hello\"" | rex field=req_host "^(?<client>[^.]*)" | bucket span=1d _time |stats count by client FIELDNAME _time | sort - _time count|dedup 3 client

Re: Join truncating the result. how to avoid it?

wpreston — Fri, 05 Jun 2015 17:45:45 GMT

Try restructuring your search to not use join at all. I think you can get what you need using stats instead. Would something like this work?

sourcetype=access_combined (index="index" tag=tag1 "def") OR (index="index" tag=tag2 "hello")
| rex "(?i)/user/(?P<FIELDNAME>[^/]+)" 
| rex field=req_host "^(?<client>[^.]*)" 
| bucket span=1d _time 
| stats count by client FIELDNAME _time 
| sort - _time count 
| dedup 3 _time

Re: Join truncating the result. how to avoid it?

sp1711 — Fri, 05 Jun 2015 17:59:01 GMT

the regex for client doesnt work with this. It gives me a wrong result on that. And still the data is only for a day. So I get 3 rows in the table, all realted to one day.

Re: Join truncating the result. how to avoid it?

sp1711 — Fri, 05 Jun 2015 18:03:59 GMT

I get Error in 'map': Did not find value for required attribute 'correlation_id'.

Re: Join truncating the result. how to avoid it?

woodcock — Fri, 05 Jun 2015 18:08:21 GMT

I had a typo but I fixed it in the original answer; try again.

Re: Join truncating the result. how to avoid it?

sp1711 — Fri, 05 Jun 2015 18:13:01 GMT

I get the same error!

Re: Join truncating the result. how to avoid it?

woodcock — Fri, 05 Jun 2015 18:43:14 GMT

Then you do not have a field called correlation_id as you clearly imply in your original search. Fix that and it will work.

Re: Join truncating the result. how to avoid it?

sp1711 — Fri, 05 Jun 2015 19:04:14 GMT

I ran just the first part of the query and checked if it has a correlation_id field. It has got it. I dont understand why the map wont work.

Re: Join truncating the result. how to avoid it?

woodcock — Fri, 05 Jun 2015 19:13:34 GMT

Try the same search but get rid of the string |fields correlation_id and see if that helps.

Re: Join truncating the result. how to avoid it?

sideview — Mon, 28 Sep 2020 20:08:41 GMT

One big problem is that right before your join you are doing fields correlation_id and thus throwing away all fields except for correlation_id. This will allow your join to work but all other subsequent search commands will lose the fields they expect (eg req_host, client, FIELDNAME) . Take that fields clause out. In general you don't have to worry about Splunk extracting and preserving too many fields. In fact the search parser does the opposite and optimizes the fields extracted and preserved, down to just fields named in the search string.

index="index" tag=tag1 sourcetype=access_combined "def"|rex "(?i)/user/(?P<FIELDNAME>[^/]+)"| bucket span=1d _time |stats count by FIELDNAME _time|where count<30 AND count>20 |join correlation_id[search index="index" tag=tag2 sourcetype=access_combined "hello"] | rex field=req_host "^(?<client>[^.]*)" | bucket span=1d _time |stats count by client FIELDNAME _time | sort - _time count|dedup 3 client

However I also strongly recommend using stats instead of join. You should absolutely avoid join for this, and it's a bread and butter case for the use of stats.

I think the reason wpreston's search doesn't work for you is that your stats needs to group by correlation_id. You want something more like this:

index="index" sourcetype=access_combined  (tag=tag1 "def") OR (tag=tag2 "hello") 
| rex "(?i)/user/(?P<FIELDNAME>[^/]+)"
| rex field=req_host "^(?<client>[^.]*)" 
| stats values(FIELDNAME) as FIELDNAME values(req_host) as req_host) values(client) as client last(_time) as _time by correlation_id
| bucket span=1d _time 
| stats count by FIELDNAME _time
|where count<30 AND count>20 
|stats count by client FIELDNAME _time 
| sort - _time count
|dedup 3 client

One other note - the rows coming into your second stats command already have a count field, so you may want sum(count) as count instead of just another count. Depends what you want to count of course, but I'd make sure you're clear on what you're getting.