Subsearch not returning any results though matches exists.

[sk_subhani]

Hi,
I am trying to run below query and the scenario is here. This is not returning any results though match exists. Can you help me?
1) My subsearch yields multiple order numbers from file 1.
2) I need to pass this list to another file, file 2 which contains super set of the order numbers resulted in my subsearch.

I want to fetch all the events which has orderNo from file 1 that are resulted in file 2.

source="dir1/file1.log" [ search host="server1" source="dir2/file2.log" | rex field=_raw "\"orderNo\":\"(?\w*-\w*-\w*-\w*-\w*)" | stats count by orderNo | table orderNumbers | format ]

I tried several variations of passing the sub search results (table, list, etc) to main search but not working. Can you please identify what is wrong here?

[woodcock]

Assuming the 2 datasets have the same format and that field orderNo does not exist and must be created, this should work:

source="dir1/file1.log" OR (host="server1" source="dir2/file2.log") | rex "\"orderNo\"\:\"(?<orderNo>\w*-\w*-\w*-\w*-\w*)"  | stats dc(source) AS numSources values(*) AS * by orderNo | where  numSources>1

In your case you can (AND ALWAYS SHOULD if you can) avoid using join and subsearches.

[jkat54]

Hello sk_subhani,

About this search...

  source="dir1/file1.log" [ search host="server1" source="dir2/file2.log" | rex field=_raw "\"orderNo\"\:\"(?\w*-\w*-\w*-\w*-\w*)" | stats count by orderNo | table orderNumbers | format ]

When you do stats count by orderNo you're left with 1 field called orderNo which equals the count. Then you're sending this one field into a table orderNumbers which doesnt exist. So you get 0 results.

If you will supply us with some sample data from both files we can help you write the correct search.

Just to give you example... you can try this but I'm making many assumptions and dont believe this search will be a sure answer:

 source="dir1/file1.log" 
 | eval orderNo=orderNumbers 
 | join orderNo [search host="server1" source="dir2/file2.log" | rex field=_raw "\"orderNo\"\:\"(?\w*-\w*-\w*-\w*-\w*)" | stats count by orderNo] 
 | table orderNumbers orderNo