topic Re: subsearch is truncating the results with join command in Splunk Search

Why is subsearch truncating the results with join command?

shashank_24 — Mon, 28 Aug 2023 16:52:05 GMT

Hi, I am trying to join 2 searches with produce some results but I am getting this error which says - "subsearch produced 50000 results truncating to 50000".

I can't change the limits.conf so is there any other way to get the stats without using join.

This is my search -

index=test_index ip="83.136.24.154" sourcetype=audit_log event=Attempt NOT messagetype=Request NOT status=failure | rex field=idDetails "id\:(?<id>.*)" | eval successful_login=if(status == "success", "Yes", "No") | rename subject AS username | join type=left id username [ search index=test_index sourcetype=server_log "validator.Credential" | rex field=_raw "id\:(?<id>[^\s]+)" | rex field=_raw "mytemp\s(?<message>.*)$" | rex field=_raw "user\s\[?(?<username>[^\]]+)" | fields id,message,username] | table _time,username,successful_login,message

Let me know if someone can advice.

Re: subsearch is truncating the results with join command

gcusello — Fri, 19 Jun 2020 12:15:17 GMT

Hi @shashank_24 ,

The only way it update limits.conf and it isn't a good idea, but you could have a different approach,.

You could use stats creating something like this (I cannot test your search, but see my approach):

(index=test_index sourcetype=audit_log event=Attempt NOT messagetype=Request NOT status=failure) OR (index=test_index sourcetype=server_log "validator.Credential") | rex field=idDetails "id\:(?<id>.*)" | rex "id\:(?<id>[^\s]+)" | rex "mytemp\s(?<message>.*)$" | rex "user\s\[?(?<username>[^\]]+)" | eval successful_login=if(status="success", "Yes", "No") | rename subject AS username | stats earliest(_time) AS _time values(successful_login) AS successful_login values(message) AS message BY id username | table _time,username,successful_login,message

Ciao.

Giuseppe

Re: subsearch is truncating the results with join command

shashank_24 — Fri, 19 Jun 2020 15:29:09 GMT

@gcusello Thanks for the response but it's not working. Actually there is an ip address in the 1st search I forgot to mention. (updated the original query) and I want it to narrow down the results to that IP but all the results are coming.

Re: subsearch is truncating the results with join command

to4kawa — Sat, 20 Jun 2020 01:28:56 GMT

index=test_index (ip="83.136.24.154" sourcetype=audit_log event=Attempt NOT messagetype=Request NOT status=failure) OR (sourcetype=server_log "validator.Credential") | rex field=idDetails "id\:(?<id>.*)" | rex field=_raw "id\:(?<id>[^\s]+)" | rex field=_raw "mytemp\s(?<message>.*)$" | rex field=_raw "user\s\[?(?<username>[^\]]+)" | eval username=coalesce(username,subject) | stats min(_time) as _time values(status) as status dc(sourcetype) as flag values(message) as message by id,username | where flag > 1 | eval successful_login=if(status="success", "Yes", "No") | table _time,username,successful_login,message

If join can't work, try stats.

Re: subsearch is truncating the results with join command

gcusello — Sat, 20 Jun 2020 06:11:39 GMT

HI @shashank_24 ,

as I said, I cannot test the search because I haven't your data, but I'd like to pass you the approach: instead join (with one or more keys) use a stats approach (as also @to4kawa is suggesting):

(main_search) OR (subsearch) | all the eval and rex you need | stats values(all_the_fields_you_need) AS field_name BY key1 key2 | table all the fields

Ciao.

Giuseppe

Re: subsearch is truncating the results with join command

shashank_24 — Mon, 22 Jun 2020 10:37:45 GMT

Hi @gcusello @to4kawa Thank you so much. You guys are stars. After tweaking both query worked for me. I have used the one which @to4kawa has mentioned with Stats so I will accept it.

Again thanks to both of you.

Re: subsearch is truncating the results with join command

ashvinpandey — Tue, 15 Jun 2021 17:56:20 GMT

Adding max=0 will show all the events
for eg.

| join type=left id username max=0

@shashank_24

Re: subsearch is truncating the results with join command

Lavender — Mon, 28 Aug 2023 06:24:26 GMT

What will be the solution if we are trying to join two savedsearches . How could we avoid the truncate issue .

I am trying to join two saved searches , but I cant able to combine two saved searches without join command . Could you please help

query is :

|loadjob savedsearch="admin:command_center:datamodel_search"

|search status!=Resolved AND status!=closed AND status!=cancelled

|join type=left inc_id

[|loadjob savedsearch ="admin:command_center:sev_datamodel_search"

|search sla_status="Completed" AND sla_type="Hold" AND flag=1

|stats sum(sla_time) as sla_complete by inc_id]