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

View solution in original post

dhirendra761
Contributor

Hi @techiesid ,

 

Thank you so much it work perfectly!.😀👍

0 Karma