Distinct values from XML array in timechart

jonathanoberhau — Wed, 07 Nov 2018 17:59:55 GMT

I am looking at an XML response from an API that contains an array of messages. I want to timechart the messages for a dashboard so we can see the count of each type of error message over time.

What is tripping me up is that sometimes a message in one response type can be included in another response type ( in addtion to other messages) and I am having a hard time separating them into distinct columns in the time chart

 index=best_index_ever  "message.location"="cancelContract"
    | spath input=message.data.responseBody output=ResponseMessages 
 path=soap:Envelope.soap:Body.CancelContractResponse.CancelContractResult.Messages.Message
    | mvexpand ResponseMessages
    | spath input=ResponseMessages
    | eval Text = case( like(Text,"%Cannot Cancel Contract.%") AND NOT like(Text,"Transaction%"),"Cannot Cancel Contract",
                        like(Text,"Transaction%"),"Transaction Deadlock",
                        1=1,Text
                      )
    | timechart limit=0 useother=false count by Text

That is what I have so far. I tried using a case with an eval, but that does not work. It still lumps the responses with a message "Transaction...deadlocked" into the "Cannot Cancel Contract" column as well as includes them in the "Transaction...deadlocked" column.

Here is an example of the XML array of messages.

Response example 1:

<Messages>
       <Message>
          <Type>1</Type>
          <Code/>
          <Text>Transaction (Process ID 75) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</Text>
       </Message>
       <Message>
          <Type>1</Type>
          <Code>SAVEFAIL</Code>
          <Text>Cannot save record.</Text>
       </Message>
       <Message>
          <Type>1</Type>
          <Code>CONTRACT_CANCEL_FAIL</Code>
          <Text>Cannot Cancel Contract.</Text>
       </Message>
</Messages>

Response example 2:

<Messages>
       <Message>
          <Type>1</Type>
          <Code>CONTRACT_CANCEL_FAIL</Code>
          <Text>Cannot Cancel Contract.</Text>
       </Message>
</Messages>

You'll see that the Cannot Cancel Contract message is in both responses, but the first response also contains two other messages. I am trying to have the timechart represent responses that only contain the "Cannot Cancel Contract" message as mutually exclusive of the messages that contain "Cannot Cancel Contract" + other messages.

topic Distinct values from XML array in timechart in Splunk Search

Distinct values from XML array in timechart