How to search for the latest field value that is not equal to a certain value?

[Glasses]

Hi
Just not having luck with my syntax.
I have proofpoint logs and I am looking for the latest final_action value that is not equal to continue...
For example
Index=Proofpoint sourcetype=mail_logs | stats latest(final_action) gives me the last value... like if it was rejected or continued
The challenge I have is searching for latest final_action != continue...
The purpose here is that the final action can change from "discard" to "continue" so I want to filter on the "latest"...

Any advice appreciated...

[masonmorales · Accepted solution]

If you want to blacklist multiple final_actions, you could do:
index=proofpoint sourcetype=mail "@somedomain.com" "connection.helo"=".somename.com"|stats latest(final_action) as final_action by msg.header.subject{} msg.header.to{} msg.header.from{} | search NOT final_action IN (continue, discard, reject)

[Glasses]

Thank you for the answer. This is a good example for blacklisting.

[mayurr98]

try this :

Index=Proofpoint sourcetype=mail_logs final_action!=continue | stats latest(final_action)

OR

Index=Proofpoint sourcetype=mail_logs 
| stats latest(eval(case(final_action!="continue",final_action))) as "final_action"

[Glasses]

Thank you for the reply but neither are what I am looking for...
There are multiple events per email that contain "final_action", if an event with final_action=discard arrives at 10:41 another event for the same email can arrive later at 10:42 where final_action=continue, this is because there are a sequence of filters checking the email...
So I only want to find emails where the last or latest final_action!=continue...
Hope that makes sense... thank you

[Glasses]

what I am trying to do is use this
index=proofpoint sourcetype=mail "@somedomain.com" "connection.helo"=".somename.com"|stats latest(final_action) by msg.header.subject{} msg.header.to{} msg.header.from{}

which gives me all the emails with the latest final_action value.... but now I need to filter out any final_action which is discard , reject etc...

any advice appreciated... Thank you

[Glasses]

apparently this works but I don't know if its the best way.... index=proofpoint sourcetype=mail "@somedomain.com" "connection.helo"=".somename.com"|stats latest(final_action) by msg.header.subject{} msg.header.to{} msg.header.from{} |WHERE final_action!="continue"

if anyone can confirm or improve, it is much appreciated...

[Glasses]

my only improvement I might need is defining the latest(final_action) as FINAL so that it looks at the values for the latest... but IDK - still validating

....| stats  latest(final_action) as FINAL  by _time msg.header.subject{} msg.header.to{}  msg.header.from{} |WHERE FINAL!="continue"

[Glasses]

nope does not look at the time, does not retain the time comparison of the final_action events... have to rewrite...

[Glasses]

Final note, the issue is that proofpoint logs have multiple filters with multiple final actions and the logs don't have a absolutely final action or "delivered" or "not delivered" status in the message log. So I have to correlate a qid field from the message logs to the mta logs and check there if the email was sent... thank you everyone for you help.

[mayurr98]

index=proofpoint sourcetype=mail "@somedomain.com" "connection.helo"=".somename.com" final_action!="continue" |stats latest(final_action) by msg.header.subject{} msg.header.to{} msg.header.from{}

this will be faster

[Glasses]

@mayurr98 thank you but if I define final_action!=continue then I might not get the latest final_action values. Each email has more than one final_action but the last or latest one indicates where it continued to deliver or got dropped /discarded... I appreciate you stay with the thread though...

[mayurr98]

then filtering at the end is the only option

[masonmorales]

This is the best way.

[mayurr98]

so you could add the actions that you want in the main search. final_action="discard" OR final_action="reject" OR...