Nested Search and Map Search- Is there a way to make this search more efficient?

[chteh]

Dear all,

I want to combine 2 search job into 1 job.
My first search job is to search all the alert_id occur in the past 24 hours and listed them as a table.

2nd search job is to find among all the alert_id in the first search job and try to match which alert_id has an event of packet filtered .

I am able to generate a desired result by using the "map search"

index="security_device" sourcetype= security_log "abnormal Protocol" alert_id
| table alert_id
| map search="search index="security_device" sourcetype=security_log "Filter action"  $alert_id$" maxsearches=500
| table filter-discard

However, I notice that using a map search is very in-efficient. It is taking forever if I select for 30 days. Can anyone recommend me a better way to do it. 
FYI, I have tried the nested search, but no luck, it return a 0 result to me 🙂

 index="security_device" sourcetype=security_log "Filter action"
              [ search index="security_device" sourcetype=security_log "abnormal Protocol" alert_id
              | table alert_id ]
| table filter-discard

Thank you.

[scelikok]

Hi @chteh,

Based on your picture, you can try below search;

 eval check=coalesce('filter-discard',alert_id) 
| stats values(*) as * by check 
| where NOT alert_id='filter-discard' AND isnotnull(alert_id) 
| fields - check

[scelikok]

Hi @chteh,

Can you try search without subsearch. I assumed your "Filter action" and "abnormal Protocol" are in filter-discard field. 

index="security_device" sourcetype=security_log ("Filter action" OR "abnormal Protocol"
| stats values(filter-discard) as filter-discard by alert_id 
| where isnotnull(mvfind('filter-discard',"Filter action"))
| table filter-discard

[chteh]

@scelikok , thanks for your reply. Your idea is good but still I can't make a good output from what you have suggested. But you suggestion definitely giving me a new idea on this search. Now I am uploading a picture of my work (table alert_id filter-discard)

What i am trying to do is based on the alert_id field, if the ID exist in the filter-discard field, that meaning my router has taken an action for that particular alert_id. 
Thank you.

[chteh · Accepted solution]

@scelikok I managed to use the nested search to improve my delay in search, and now my search job did not take forever when I go for 30 days search duration:

index="security_device" sourcetype=security_log "Filter action" 
     [ index="security_device" sourcetype=security_log "abnormal Protocol" alert_id 
     | dedup alert_id 
     | stats count by alert_id 
     | rename alert_id as query 
     | fields query 
     | format] 
| rex "Alert\s(?<ext_filter-discard>[0-9]{7})" 
| dedup ext_filter-discard 
| table ext_filter-discard 
| stats count

The reason for using the dedup in alert_id and filter-discard is because each event it will have a "start" and "end" event, so what I just need 1 of them. 
So, the inner search will provide me all the alert_id in the log and the outer search will help me to find and match which of the alert_id actually associate with an action, which is filter-discard.

Here is the result of my inner search:

( ( 4686463 ) OR ( 4686624 ) OR ( 4686638 ) OR ( 4686656 ) OR ( 4686679 ) OR ( 4686698 ) OR ( 4686744 ) OR ( 4686783 ) OR ( 4686802 ) OR ( 4686825 ) OR ( 4686853 ) OR ( 4686881 ) OR ( 4686902 ) OR ( 4686927 ) OR ( 4686963 ) OR ( 4686996 ) OR ( 4687020 ) OR ( 4687088 ) OR ( 4687118 ) OR ( 4687359 ) OR ( 4687386 ) OR ( 4687409 ) OR ( 4687697 ) OR ( 4687746 ) OR ( 4687775 ) OR ( 4687801 ) OR ( 4687825 ) OR ( 4687855 ) OR ( 4687877 ) OR ( 4687896 ) )

Thanks again for your help @scelikok 

[scelikok]

Hi @chteh , nice to hear your issue resolved.