Passing a value from one search to another (map? subsearch? join?)

[chris24747]

Hi,

This is my first question - usually I've been able to figure it out following posts on here but I'm struggling with this one a little as I'm struggling to get my head around joins, sub-searches and maps.

What i am trying to do is search through my e-mail message tracking logs and identify the top 10 subject lines of a message and is marked as junk. Then i want to do the same search but based on the message subject where it hasn't been flagged as junk. I then want it shown in a table as per

MessageSubject     Count of mails marked as junk    Count of mails not marked as junk
subject 1                        10                                 5

this should show only the top 10 (or whatever i change it to) that have been marked as junk, the number not marked as junk is just for reference, no sorting etc required.
So far i have tried Join:

_index=msgtrack event_id=DELIVER | stats count as MarkedAsJunk  | join message_subject [search index=msgtrack recipient_status="*<Junk Email>*" event_id=DELIVER | top limit=10 message_subject | stats count as NotFilteredOut] | table message_subject MarkedAsJunk NotFilteredOut_

map

_index=msgtrack recipient_status="*<Junk Email>*" event_id=DELIVER | top limit=10 message_subject | map maxsearches=2000 search="index=msgtrack event_id=DELIVER message_subject=$message_subject$ | stats count as NotFilteredOut"_  (work in progress)

subsearch
(didnt save this one, sorry)

Any pointers would be great, at the moment I'm not even sure which approach I should be coming from.
Thanks

[jacobwilkins · Accepted solution]

You crazy kids make things sofa king hard sometimes!

index=msgtrack event_id=DELIVER 
| stats count(eval(recipient_status=="<Junk Email>")) as MarkedAsJunk,  count(eval(NOT (recipient_status=="<Junk Email>"))) AS NotFilteredOut by message_subject 
| sort - MarkedAsJunk
| head 10

Does that do what you want? Always do things in a single pass if you can, unless you need long-term trending.

The example with map is terrible, as it causes a total of 11 distinct searches to be dispatched.

[chris24747]

See, this is why i ended up asking! 🙂 Getting close. I typo'd my above search, i need the recipient_status to equal "<Junk EMail>" (ie adding the additional wildcards) which eval is not going to like!

(Ive got a star (ie shift+8) either side of the <Junk Email> above, but within the quotes. This page wont display it.

[jacobwilkins]

What wildcards?

Just a case sensitivity issue with the string?

| stats count(eval(match(recipient_status,"(?i)<Junk Email>"))) as MarkedAsJunk,  count(eval(NOT (match(recipient_status,"(?i)<Junk Email>")))) AS NotFilteredOut by message_subject 

I usually use match()in that context anyway, the differences between ==and =can be confusing. You could probably also use searchmatch(), which might make wildcarding easier if you aren't down with regular expressions.

Really, you have tons of options.

| chart count over message_subject by recipient_status 

^^ Another approach.

[chris24747]

The stars aren't showing on my above post. but I realised I don't need then as its a regex anyway.

Anyway, I'm getting somewhere now after you guy's help. I just need to make it sort the 'MarkedasJunk' column and remove any rows which equal 0.

[woodcock]

Try this:

index=msgtrack recipient_status="<Junk Email>" event_id=DELIVER | top limit=10 message_subject | map maxsearches=2000 search="index=msgtrack event_id=DELIVER message_subject=\"$message_subject$\" | stats count AS all count(eval(recipient_status="<Junk Email>") AS junk BY message_subject

[chris24747]

thanks for the response.. So i dont get any Parsing errors with this but i get an error on the job (one that i've seen before, so i must have been on the right track. the error says "Unable to run query 'index=msgtrack event_id=DELIVER message_subject="Gardeners 250ml Hand Therapy | Back by popular demand" | stats count AS all count(eval(recipient_status='<Junk Email>') AS junk BY message_subject'."

the good news is that the message subject that has been added to the search is the top result from the first search, so this is close but something else is going on. Which log file do i need to look into to find this error?

[woodcock]

I re-edited the original answer to add double-quotes around the subject string so parts of it will not be interpreted as commands; try updated answer.

[chris24747]

still the same error, i've also tried making the = to == in the eval() to no avail. When i inspect the job i can see that the quotes are being escaped ok. For what it's worth i expect its going to be this way, with a map, thats going to get me what i want.