topic Re: Nested search to identify null counts in Splunk Search

Nested search to identify null counts

pinalshah341 — Tue, 13 Jul 2021 02:15:43 GMT

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 -

I am expecting output like -

ABCD 0

Re: Nested search to identify null counts

venkatasri — Tue, 13 Jul 2021 03:05:18 GMT

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!

Re: Nested search to identify null counts

venkatasri — Tue, 13 Jul 2021 03:04:23 GMT

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!

Re: Nested search to identify null counts

pinalshah341 — Tue, 13 Jul 2021 03:04:56 GMT

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.

Re: Nested search to identify null counts

venkatasri — Tue, 13 Jul 2021 04:04:08 GMT

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