Help with Splunk search based on eventType dynamic table value

[dmuley]

Hello Team, I am new to splunk and have requirement to create table based on raw data

This is how the data looks in splunk

Date  threadId=ABC123   eventType=”InMsg” data=”<rootrq><a>hi</a></rootrq>”

Date  threadId=ABC123   eventType=”thirdPartyReq” data=”<root1req><a>hi</a></root1req>”

Date  threadId=ABC123   eventType=” thirdPartyRes” data=”<root1res><a>hi</a></root1res>”

Date  threadId=ABC123   eventType=”OutMsg” data=”<rootrs><a>hi</a></rootrs>”

and wanted to create table like below. Please can some one help? threadId is common for all four records.

index=test | 

date	threadId	InMsg	OutMsg	thirdPartyreq	thirdprtyRes
date	ABC123	<rootrq><a>hi</a></rootrq>	<rootrs><a>hi</a></rootrs>	<root1req><a>hi</a></root1req>	<root1res><a>hi</a></root1res>

[dmuley]

@ITWhisperer this works thank you. also do you mind in sharing how can I remove double quotes from xml String having attribute ? 

<root1res>
          <a  test="testdata">hi</a>
</root1res>

Currently after pulling info I m just receiving upto <a test=

I already tried   | eval data1=replace(data,"\"","")  but its not working

[ITWhisperer]

It sounds like your embedded quotes haven't been escaped and/or your extraction isn't taking embedded quotes into account. How are you extracting the data field?

[dmuley]

frankly not sure. dealing with splunk admin from company tooks longer than fixing by our self. Is there any way to extract key value from xml ? @ITWhisperer I really appreciate your help on this. 

[ITWhisperer]

You can re-extract the fields from the _raw event field. Can you share your raw events (anonymised of course) in a code block </> so we can better see what you are dealing with?

[dmuley]

<TestRQ><Device_Info><Device_Type>GATEWAY</Device_Type><conf Name="test1"/><conf Name="test2">NONE</conf><conf Name="test3">Y</conf></Device_Info><Request_Version>3.0</Request_Version><EMV><TAG isEncrypted="false" sierra="Y" tagDescription="KernalVersionNumber" tagLength="0F" tagName="DF79">DF790F36</TAG></EMV></TestRQ>

This is how one of the data field looks like in  our 1 event and I want extract all fields that are in data . @ITWhisperer 

2022-06-12 21:51:42.274 threadId=L4C9D6WIYK2K class="HttpConnector" mname="Adapter_Connector" callId="F2JAMR29ZCE5" eventType="REQUEST" data="<TestRQ><Device_Info><Device_Type>GATEWAY</Device_Type><conf Name="test1"/><conf Name="test2">NONE</conf><conf Name="test3">Y</conf></Device_Info><Request_Version>3.0</Request_Version><EMV><TAG isEncrypted="false" sierra="Y" tagDescription="KernalVersionNumber" tagLength="0F" tagName="DF79">DF790F36</TAG></EMV></TestRQ>"

[dmuley]

@ITWhisperer Please can you help to get root name of xml under data node and add it in table with count of occurences per thread ?

2022-06-12 21:51:42.274 threadId=L4C9D6WIYK2K class="HttpConnector" mname="Adapter_Connector" callId="F2JAMR29ZCE5" eventType="REQUEST" data="<TestRQ><Device_Info><Device_Type>GATEWAY</Device_Type><conf Name="test1"/><conf Name="test2">NONE</conf><conf Name="test3">Y</conf></Device_Info><Request_Version>3.0</Request_Version><EMV><TAG isEncrypted="false" sierra="Y" tagDescription="KernalVersionNumber" tagLength="0F" tagName="DF79">DF790F36</TAG></EMV></TestRQ>"

I need to get

data	count
TestRQ 1	20
OtherRQ	10

etc

[ITWhisperer]

| rex "data=\"\<(?<data>[^\>]+)"

[ITWhisperer]

I am a little confused. Your data field looks complete, i.e. it doesn't end at the first double quote. 

[dmuley]

index="test" eventType="*"
| eval length=len(threadId) | where length = 12
| eval {eventType}=data
| stats values(*) as * by threadId
| table threadId REQUEST RESPONSE

When I am using above query it only displays xml till  conf tag's name attribute and removes all contents after that.

<TestRQ><Device_Info><Device_Type>GATEWAY</Device_Type><conf Name=

I am expecting to recieve full xml in tables column. @ITWhisperer 

[ITWhisperer]

Assuming data is the last field in your event, you can re-extract it with some rex

index="test" eventType="*"
| eval length=len(threadId) | where length = 12
| rex "data=\"(?<data>.*)\"$"
| eval {eventType}=data
| stats values(*) as * by threadId
| table threadId REQUEST RESPONSE

[dmuley]

Perfect it works. thank you much @ITWhisperer 

[ITWhisperer · Accepted solution]

Assuming you already have extracted these fields, you could do something like this

| eval {eventType}=data
| stats values(*) as * by Date threadId