Nested search to identify null counts

[pinalshah341]

Below are my 2 log lines - 

1.Successfully received message RECEIVED, payload={\"reference_id\":\"ABCD\"...}

2. Successfully published COMPLETED,  payload=(referenceId=ABCD,...

For the given referenceId ABCD, I want to search if "COMPLETED" message was published or not. 

I am trying to do nested search but not getting the right result - 

index=xyz "Successfully *"  "COMPLETED"  | rex "referenceId=(?<referenceId>[^,]*).*" | join reference_id in [search index=xyz  "Successfully * message" AND ("RECEIVED") | rex "reference_id\\\\\":\\\\\"(?<reference_id>[^\\\\]*).*" | dedup reference_id | fields reference_id] | stats count by referenceId | where count < 1

I am expecting output like - 

ABCD 0

[venkatasri]

Hi @pinalshah341 

Can you try this, start using from rex command before that it was for testing. if the count is <= 1 that means you have no Completed status associated to referenceId.

| makeresults 
| eval _raw="Successfully received message RECEIVED, payload={\"reference_id\":\"ABCD\"...} | Successfully published COMPLETED,  payload=(referenceId=ABCD,..." 
| makemv delim="|" _raw 
| mvexpand _raw 
| rex field=_raw "payload\=\{\\\"reference_id\\\":\\\"(?<ReferenceID>\w+)" 
| rex field=_raw "payload\=\(referenceId\=(?<ReferenceID>[^\,]+)" 
| stats count by ReferenceID 
| where count <= 1

---

An upvote would be appreciated and Accept Solution if this reply helps!

[pinalshah341]

The problem with this approach is, if a "RECEIVED" message is published in last 1 min of the search range i.e. "COMPLETED" message is still in processing state, it will falsely show up in the table. To avoid that, I was going by the nested search approach. Let me know if this usecase can be fixed using the same query you suggested.

[venkatasri]

@pinalshah341 When you change the condition to | where count > 1 would only provide events completed.

<=1 would be either in progress or not completed. subsearch with join vs combining results would achieve the same results.

[venkatasri]

Your query would be something like this,

index=xyz "Successfully *"  "COMPLETED" 
| rex field=_raw "payload\=\{\\\"reference_id\\\":\\\"(?<ReferenceID>\w+)" 
| rex field=_raw "payload\=\(referenceId\=(?<ReferenceID>[^\,]+)" 
| stats count by ReferenceID 
| where count <= 1

---

An upvote would be appreciated and Accept Solution if this reply helps!