Why is subsearch truncating the results with join command?

[shashank_24]

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. 

[ashvinpandey]

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

| join type=left id username max=0

 @shashank_24 

[gcusello]

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

[shashank_24]

@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.

[gcusello]

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

[shashank_24]

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.

[to4kawa · Accepted solution]

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.

[Lavender]

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]