how to display the difference between main search and subsearch

[mm7]

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?

[gcusello · Accepted solution]

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]

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]

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

thank you!

[mm7]

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