Splunk Search

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

Glasses
Builder

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

Tags (1)
1 Solution

masonmorales
Influencer

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)

View solution in original post

0 Karma

masonmorales
Influencer

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)

0 Karma

Glasses
Builder

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

0 Karma

mayurr98
Super Champion

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
Builder

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

0 Karma

Glasses
Builder

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

0 Karma

Glasses
Builder

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

0 Karma

Glasses
Builder

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"
0 Karma

Glasses
Builder

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

0 Karma

Glasses
Builder

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.

0 Karma

mayurr98
Super Champion
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
Builder

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

0 Karma

mayurr98
Super Champion

then filtering at the end is the only option

0 Karma

masonmorales
Influencer

This is the best way.

0 Karma

mayurr98
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...