Splunk Search

how to display the difference between main search and subsearch

mm7
Explorer

assuming I have this log history:

[sent] task=abc, id=123

[sent] task=abc, id=456

[success] task=abc, id=123

I would like to get a list of all ids that are "sent" but did not get a "success", so in the above example it should just be "456"

my current query looks something like this

 

"abc" AND "sent"
| table id
| rename id as "sent_ids"
| appendcols [
  search "abc" AND "success"
  | table id
  | rename id as "success_ids"
]

 

 

this gets me a table with the 2 columns, and I'm stuck on how to "left exclusive join" the two columns to get the unique ids.

or maybe I'm approaching this entirely wrong and there is a much easier solution?

Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @mm7 ,

please try something like this:

<your_search>
| rex "^\[(?<status>[^\]]*)"
| stats dc(status) AS status_count values(status) AS status BY task id
| where status_count=1 AND status="sent"
| table task id status

ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @mm7 ,

please try something like this:

<your_search>
| rex "^\[(?<status>[^\]]*)"
| stats dc(status) AS status_count values(status) AS status BY task id
| where status_count=1 AND status="sent"
| table task id status

ciao.

Giuseppe

gcusello
SplunkTrust
SplunkTrust

Hi @mm7 ,

you could extract the status field as a permanent field so you don't need to extract in the search or use eval(searchmatch) but this is the faster way.

Ciao.

Giuseppe

mm7
Explorer

wow this is so much cleaner and faster! did not think to regex out the status string

thank you!

0 Karma

mm7
Explorer

figured it out, changed "appendcols" to "append" and added this to the end

 

| stats count(id) AS "count" by id
| where count==1

 

there is probably a better way, open to take other answers, thanks!

EDIT: accepted a way better solution

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...