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:
TextID | TO | STATUS |
123A | To1 | sent |
123A | To1 | deferred |
234B | To2 | sent |
234B | To2 | delayed |
345C | To3 | sent |
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:
TextID | TO | STATUS | FROM |
123A | To1 | sent | From1 |
123A | To1 | deferred | From1 |
234B | To2 | sent | From2 |
234B | To2 | delayed | From2 |
345C | To3 | sent | From1 |
Thank you
Jörg
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
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
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.
@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
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
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