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