topic Re: Extract values from field conditionally on other field value in Splunk Search

Extract values from field conditionally on other field value

dhirendra761 — Wed, 13 Jan 2021 22:19:59 GMT

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.

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

Re: Extract values from field conditionally on other field value

dhirendra761 — Thu, 14 Jan 2021 08:32:37 GMT

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

Thank you.

Re: Extract values from field conditionally on other field value

to4kawa — Thu, 14 Jan 2021 09:15:04 GMT

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

Re: Extract values from field conditionally on other field value

dhirendra761 — Thu, 14 Jan 2021 10:52:28 GMT

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.

Re: Extract values from field conditionally on other field value

to4kawa — Thu, 14 Jan 2021 11:07:38 GMT

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

Re: Extract values from field conditionally on other field value

dhirendra761 — Thu, 14 Jan 2021 11:18:12 GMT

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?

Re: Extract values from field conditionally on other field value

to4kawa — Thu, 14 Jan 2021 11:57:02 GMT

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

Re: Extract values from field conditionally on other field value

techiesid — Thu, 14 Jan 2021 13:37:45 GMT

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

Re: Extract values from field conditionally on other field value

dhirendra761 — Thu, 14 Jan 2021 15:26:30 GMT

Hi @techiesid ,

Thank you so much it work perfectly!.😀👍

Re: Extract values from field conditionally on other field value

dhirendra761 — Thu, 14 Jan 2021 15:41:27 GMT

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