Splunk Search

Add value from sub search into the result set of the main search

Joerg
Explorer

I've a sub search on an SMTP log to get all TO and FROM values together with the status. Unfortunately TO and FROM are in one log entry and TO and STATUS in a different one. Common field is the TextID. 
Simplified the log structure looks like the following for a single TextID:

 

...
{"id":null,"log":{"text":"123A: to=<T@>, status=sent"}}
{"id":null,"log":{"text":"123A: to=<T@>, status=deferred"}}
{"id":null,"log":{"text":"123A: from=<F@> to=<T@> proto=ESMTP"}}
...

 

My current search:

 

index=A 
[ search index=A "to=<"
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> "
| dedup TextID
| return 1000000 $TextID
]
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| table TextID TO STATUS

 

My current result:

TextIDTOSTATUS
123ATo1sent
123ATo1deferred
234BTo2sent
234BTo2delayed
345CTo3sent

 

How can I also print out the FROM which is only available in the sub search in the result set of the main search?
I already tried to resolve this with union, join, append, appendcols but was unable to get expected result. Expected result would be:

TextIDTO STATUSFROM
123ATo1sentFrom1
123ATo1deferredFrom1
234BTo2sentFrom2
234BTo2delayedFrom2
345CTo3sentFrom1


Thank you
Jörg

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
index=A 
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> proto=(?<PROTO>.*)\""
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| stats values(STATUS) as STATUS values(TO) as TO values(FROM) as FROM values(PROTO) as PROTO by TextID

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

index=A 
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> "
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| stats values(STATUS) as STATUS values(TO) as TO values(FROM) as FROM by TextID
0 Karma

Joerg
Explorer

Thanks @ITWhisperer unfortunately FROM is still empty.
I also have to admit that I missed to add a condition (proto=ESMTP) to my current search. This condition is essential for the sub search to return only specific entries:

index=A 
[ search index=A AND "to=<" AND "proto=ESMTP"
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> "
| dedup TextID
| return 1000000 $TextID
]
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| table TextID TO STATUS

 Unfortunately TextID, TO, FROM, proto are in one log entry (sub search) and TextID, TO, STATUS in a different one (main search). Common for both is the TextID, TO. 

0 Karma

Joerg
Explorer

@ITWhisperer all of a sudden I got your example working and the FROM is now showing. No idea why it wasn't working the first time.  Sorry for the hassle.
But as I missed to give some important info (additional search criteria for the sub query) it also returns entries without
... "to=<" AND "proto=ESMTP"...

| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> "

Can I somehow include additional search criteria as well similar to what I've in my sub search?

Thanks Jörg

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index=A 
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> proto=(?<PROTO>.*)\""
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| stats values(STATUS) as STATUS values(TO) as TO values(FROM) as FROM values(PROTO) as PROTO by TextID

PickleRick
Ultra Champion

You can do pretty much whatever you want in splunk 😉

If you want to restrict your results only to those that have proto=ESMTP, just extract that field, then add it as another stats to calculate and filter to include only those having ESMTP in proto field.

index=A 
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*) proto=(?<PROTO>.*)> "
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| stats values(STATUS) as STATUS values(TO) as TO values(FROM) as FROM values(PROTO) as PROTO by TextID
| search PROTO=ESMTP

 

*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>