Extract values from field conditionally on other field value

[dhirendra761]

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>

[to4kawa]

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

[dhirendra761]

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

Timestamp	File	Status	Message
12/15/2020 2:45:11 AM.226	File 1	Failed	No files found. Stopped.
1/6/2021 2:45:05 AM.587	File 2	Passed	Download of file.txt succeeded.

[to4kawa]

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

[dhirendra761]

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?

[to4kawa]

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

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

[dhirendra761]

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

Thank you.

[techiesid · Accepted solution]

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]

Hi @techiesid ,

Thank you so much it work perfectly!.😀👍