Splunk Search

Extract values from field conditionally on other field value

dhirendra761
Contributor

Hi Splunkers,

Below is my issue:

Having multiple xml files, I need to monitor all the files and extracted the values from Status (Failed or Passed) and Message.

1) If status = Failed then extract the "2nd last" message of LogItem value (ex: No files found. Stopped. )

1) If status = Passed then extract the "last" message of LogItem value (ex: Download of file.txt succeeded. )

I am trying as below but need to correct it.

Spoiler
<search> | spath output=Message path=LogFile.LogItem.Message{2}
| spath output=Timestamp path=LogFile.LogItem{@Timestamp}
| spath output=Status path=LogFile.LogItem{@Status}
| stats last(eval(Status="Passed")) as Passed_Status first(eval(Status="Failed")) as Failed_Status last(Timestamp) as Timestamp last(Message) as last_Message first(Message) as first_Message by source

Thank you in advance!.

 

FIRST FILE:
<LogFile>
<LogItem Timestamp="12/15/2020 2:45:04 AM.412" Priority="0" Status="Neutral" Sequence="1">
<Message>Download start at 12/15/2020 2:45:04 AM </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="12/15/2020 2:45:04 AM.414" Priority="0" Status="Neutral" Sequence="2">
<Message>Setup Configuration</Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="12/15/2020 2:45:04 AM.420" Priority="0" Status="Neutral" Sequence="3">
<Message>Session starts to connect. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="12/15/2020 2:45:08 AM.797" Priority="0" Status="Passed" Sequence="4">
<Message>Session connected successfully. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="12/15/2020 2:45:08 AM.799" Priority="0" Status="Neutral" Sequence="5">
<Message>starts to tranfer file. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="12/15/2020 2:45:11 AM.226" Priority="0" Status="Failed" Sequence="6">
<Message>No files found. Stopped. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="12/15/2020 2:45:11 AM.345" Priority="0" Status="Failed" Sequence="7">
<Message>Error StackTrace: at XXX.Program.Main(String[] args) </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
</LogFile>
===================================================================
SECOND File:
<LogFile>
<LogItem Timestamp="06/12/2020 10:25:04.69" Priority="0" Status="Neutral" Sequence="1">
<Message>Download start at 06/12/2020 10:25:04 </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="06/12/2020 10:25:04.72" Priority="0" Status="Neutral" Sequence="2">
<Message>Setup Configuration</Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="06/12/2020 10:25:04.78" Priority="0" Status="Neutral" Sequence="3">
<Message>Session starts to connect. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="06/12/2020 10:25:05.243" Priority="0" Status="Passed" Sequence="4">
<Message>Session connected successfully. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="06/12/2020 10:25:05.246" Priority="0" Status="Neutral" Sequence="5">
<Message>starts to tranfer file. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="1/6/2021 2:45:05 AM.587" Priority="0" Status="Passed" Sequence="6">
<Message>Session connected successfully. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
<LogItem Timestamp="1/6/2021 2:45:08 AM.274" Priority="0" Status="Passed" Sequence="7">
<Message>Download of file.txt succeeded. </Message>
<StackTrace Depth="1" Method="XXX.Program.Main"/>
</LogItem>
</LogFile>

 

 

 

 

Labels (3)
0 Karma
1 Solution

techiesid
SplunkTrust
SplunkTrust

Hi Dhirendra,

Can you try the below query,

index=_internal | head 1 | fields _raw _time
|eval _raw="<LogFile>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.412\" Priority=\"0\" Status=\"Neutral\" Sequence=\"1\">
<Message>Download start at 12/15/2020 2:45:04 AM </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.414\" Priority=\"0\" Status=\"Neutral\" Sequence=\"2\">
<Message>Setup Configuration</Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.420\" Priority=\"0\" Status=\"Neutral\" Sequence=\"3\">
<Message>Session starts to connect. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:08 AM.797\" Priority=\"0\" Status=\"Passed\" Sequence=\"4\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:08 AM.799\" Priority=\"0\" Status=\"Neutral\" Sequence=\"5\">
<Message>starts to tranfer file. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:11 AM.226\" Priority=\"0\" Status=\"Failed\" Sequence=\"6\">
<Message>No files found. Stopped. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:11 AM.345\" Priority=\"0\" Status=\"Failed\" Sequence=\"7\">
<Message>Error StackTrace: at XXX.Program.Main(String[] args) </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
</LogFile>", source = "file1"
| appendpipe [
| eval _raw="<LogFile>
<LogItem Timestamp=\"06/12/2020 10:25:04.69\" Priority=\"0\" Status=\"Neutral\" Sequence=\"1\">
<Message>Download start at 06/12/2020 10:25:04 </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:04.72\" Priority=\"0\" Status=\"Neutral\" Sequence=\"2\">
<Message>Setup Configuration</Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:04.78\" Priority=\"0\" Status=\"Neutral\" Sequence=\"3\">
<Message>Session starts to connect. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:05.243\" Priority=\"0\" Status=\"Passed\" Sequence=\"4\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:05.246\" Priority=\"0\" Status=\"Neutral\" Sequence=\"5\">
<Message>starts to tranfer file. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"1/6/2021 2:45:05 AM.587\" Priority=\"0\" Status=\"Passed\" Sequence=\"6\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"1/6/2021 2:45:08 AM.274\" Priority=\"0\" Status=\"Passed\" Sequence=\"7\">
<Message>Download of file.txt succeeded. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
</LogFile>", source = "file2"]
| spath LogFile.LogItem{@Status} output=status
| spath LogFile.LogItem.Message output=Message
| table source,status,Message
| eval latest_status = mvindex(status,-1)
| eval Final_Msg = case(latest_status="Failed",mvindex(Message,-2),latest_status="Passed",mvindex(Message,-1))

View solution in original post

to4kawa
Ultra Champion
index=_internal | head 1 | fields _raw _time
|eval _raw="<LogFile>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.412\" Priority=\"0\" Status=\"Neutral\" Sequence=\"1\">
<Message>Download start at 12/15/2020 2:45:04 AM </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.414\" Priority=\"0\" Status=\"Neutral\" Sequence=\"2\">
<Message>Setup Configuration</Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.420\" Priority=\"0\" Status=\"Neutral\" Sequence=\"3\">
<Message>Session starts to connect. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:08 AM.797\" Priority=\"0\" Status=\"Passed\" Sequence=\"4\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:08 AM.799\" Priority=\"0\" Status=\"Neutral\" Sequence=\"5\">
<Message>starts to tranfer file. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:11 AM.226\" Priority=\"0\" Status=\"Failed\" Sequence=\"6\">
<Message>No files found. Stopped. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:11 AM.345\" Priority=\"0\" Status=\"Failed\" Sequence=\"7\">
<Message>Error StackTrace: at XXX.Program.Main(String[] args) </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
</LogFile>"
| appendpipe [
| eval _raw="<LogFile>
<LogItem Timestamp=\"06/12/2020 10:25:04.69\" Priority=\"0\" Status=\"Neutral\" Sequence=\"1\">
<Message>Download start at 06/12/2020 10:25:04 </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:04.72\" Priority=\"0\" Status=\"Neutral\" Sequence=\"2\">
<Message>Setup Configuration</Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:04.78\" Priority=\"0\" Status=\"Neutral\" Sequence=\"3\">
<Message>Session starts to connect. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:05.243\" Priority=\"0\" Status=\"Passed\" Sequence=\"4\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:05.246\" Priority=\"0\" Status=\"Neutral\" Sequence=\"5\">
<Message>starts to tranfer file. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"1/6/2021 2:45:05 AM.587\" Priority=\"0\" Status=\"Passed\" Sequence=\"6\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"1/6/2021 2:45:08 AM.274\" Priority=\"0\" Status=\"Passed\" Sequence=\"7\">
<Message>Download of file.txt succeeded. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
</LogFile>"]
| spath LogFile output=Logfile
| streamstats count as session
| stats count by Logfile session
| rex field=Logfile mode=sed "s/(?ms)(LogItem\>)/\1#/g"
| makemv delim="#" Logfile
| mvexpand Logfile
| spath input=Logfile
| rename LogItem.* as LogItem_*, *{@*} as *_*
| sort session LogItem_Sequence
| fields - count Logfile
0 Karma

dhirendra761
Contributor

Hi @to4kawa 

Thanks for reply. But in this spl I am getting all the extracted field.

For more information. I have posted 2 files. one is containing "Failed" and other is "Passed" in the last 2 LogItem tag.

I just wanted to extract the as below:

Timestamp

TimestampFileStatusMessage
12/15/2020 2:45:11 AM.226File 1FailedNo files found. Stopped.
1/6/2021 2:45:05 AM.587File 2PassedDownload of file.txt succeeded.
0 Karma

to4kawa
Ultra Champion
....
| where  match(LogItem_Message,"Stopped|succeeded")

I don't think it can be judged on your terms.
How can you tell the difference between "Failed" and "Passed"?

0 Karma

dhirendra761
Contributor

Yes you are right. But I was thinking to extract 2nd last message when status=failed otherwise extract last message.

Something by stats command.

Isn't possible?

0 Karma

to4kawa
Ultra Champion
...
| eventstats max(LogItem_Sequence) as last_sequence by session
| where (LogItem_Status="Failed" AND LogItem_Sequence=last_sequence - 1) OR (LogItem_Status="Passed" AND LogItem_Sequence=last_sequence)

dhirendra761
Contributor

Hi @to4kawa ,

Thank for answers, I am getting exact result after apply your suggested query.👍

<search>.....| spath LogFile output=Logfile
| streamstats count as session
| stats count first(source) as source by Logfile session
| rex field=Logfile mode=sed "s/(?ms)(LogItem\>)/\1#/g"
| makemv delim="#" Logfile
| mvexpand Logfile
| spath input=Logfile
| rename LogItem.* as LogItem_*, *{@*} as *_*
| eventstats max(LogItem_Sequence) as last_sequence first(source) by session
| where (LogItem_Status="Failed" AND LogItem_Sequence=last_sequence - 1) OR (LogItem_Status="Passed" AND LogItem_Sequence=last_sequence)

Although, I accepted the other answer as it was too simple to understand.

Thank you for your support and time. 😀

0 Karma

dhirendra761
Contributor

Hi @somesoni2@woodcock , @sdchakraborty Any input on this please.

Thank you.

Tags (1)
0 Karma

techiesid
SplunkTrust
SplunkTrust

Hi Dhirendra,

Can you try the below query,

index=_internal | head 1 | fields _raw _time
|eval _raw="<LogFile>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.412\" Priority=\"0\" Status=\"Neutral\" Sequence=\"1\">
<Message>Download start at 12/15/2020 2:45:04 AM </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.414\" Priority=\"0\" Status=\"Neutral\" Sequence=\"2\">
<Message>Setup Configuration</Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:04 AM.420\" Priority=\"0\" Status=\"Neutral\" Sequence=\"3\">
<Message>Session starts to connect. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:08 AM.797\" Priority=\"0\" Status=\"Passed\" Sequence=\"4\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:08 AM.799\" Priority=\"0\" Status=\"Neutral\" Sequence=\"5\">
<Message>starts to tranfer file. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:11 AM.226\" Priority=\"0\" Status=\"Failed\" Sequence=\"6\">
<Message>No files found. Stopped. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"12/15/2020 2:45:11 AM.345\" Priority=\"0\" Status=\"Failed\" Sequence=\"7\">
<Message>Error StackTrace: at XXX.Program.Main(String[] args) </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
</LogFile>", source = "file1"
| appendpipe [
| eval _raw="<LogFile>
<LogItem Timestamp=\"06/12/2020 10:25:04.69\" Priority=\"0\" Status=\"Neutral\" Sequence=\"1\">
<Message>Download start at 06/12/2020 10:25:04 </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:04.72\" Priority=\"0\" Status=\"Neutral\" Sequence=\"2\">
<Message>Setup Configuration</Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:04.78\" Priority=\"0\" Status=\"Neutral\" Sequence=\"3\">
<Message>Session starts to connect. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:05.243\" Priority=\"0\" Status=\"Passed\" Sequence=\"4\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"06/12/2020 10:25:05.246\" Priority=\"0\" Status=\"Neutral\" Sequence=\"5\">
<Message>starts to tranfer file. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"1/6/2021 2:45:05 AM.587\" Priority=\"0\" Status=\"Passed\" Sequence=\"6\">
<Message>Session connected successfully. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
<LogItem Timestamp=\"1/6/2021 2:45:08 AM.274\" Priority=\"0\" Status=\"Passed\" Sequence=\"7\">
<Message>Download of file.txt succeeded. </Message>
<StackTrace Depth=\"1\" Method=\"XXX.Program.Main\"/>
</LogItem>
</LogFile>", source = "file2"]
| spath LogFile.LogItem{@Status} output=status
| spath LogFile.LogItem.Message output=Message
| table source,status,Message
| eval latest_status = mvindex(status,-1)
| eval Final_Msg = case(latest_status="Failed",mvindex(Message,-2),latest_status="Passed",mvindex(Message,-1))

dhirendra761
Contributor

Hi @techiesid ,

 

Thank you so much it work perfectly!.😀👍

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