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