topic Help with Splunk search based on eventType dynamic table value in Splunk Search

Help with Splunk search based on eventType dynamic table value

dmuley — Mon, 13 Jun 2022 18:53:14 GMT

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>

Re: Splunk search based on eventType dynamic table value

ITWhisperer — Sun, 12 Jun 2022 09:13:00 GMT

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

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

Re: Splunk search based on eventType dynamic table value

dmuley — Mon, 13 Jun 2022 04:41:58 GMT

@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

Re: Splunk search based on eventType dynamic table value

ITWhisperer — Mon, 13 Jun 2022 04:50:21 GMT

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?

Re: Splunk search based on eventType dynamic table value

dmuley — Mon, 13 Jun 2022 04:53:53 GMT

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.

Re: Splunk search based on eventType dynamic table value

ITWhisperer — Mon, 13 Jun 2022 04:57:11 GMT

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?

Re: Splunk search based on eventType dynamic table value

dmuley — Mon, 13 Jun 2022 05:09:10 GMT

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

Re: Splunk search based on eventType dynamic table value

ITWhisperer — Mon, 13 Jun 2022 05:23:31 GMT

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

Re: Splunk search based on eventType dynamic table value

dmuley — Mon, 13 Jun 2022 06:29:59 GMT

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

Re: Splunk search based on eventType dynamic table value

ITWhisperer — Mon, 13 Jun 2022 06:41:09 GMT

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

Re: Splunk search based on eventType dynamic table value

dmuley — Mon, 13 Jun 2022 07:24:31 GMT

Perfect it works. thank you much @ITWhisperer

Re: Splunk search based on eventType dynamic table value

dmuley — Wed, 15 Jun 2022 10:25:57 GMT

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

I need to get

data	count
TestRQ 1	20
OtherRQ	10

etc

Re: Splunk search based on eventType dynamic table value

ITWhisperer — Wed, 15 Jun 2022 10:31:02 GMT

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