topic Re: Why am I unable to group by an xmlkv field? in Splunk Search

Why am I unable to group by an xmlkv field?

riotto — Tue, 16 Aug 2016 19:24:34 GMT

I am trying to group events and get the delta _time. This search returns the events I want to group.
The events are XML. I cannot get the events to group by the clientid.

Here's what I have so far:

index="personalizedoffer" earliest="08/16/2016:00:00:00" (XML_INPUT_LOGGER AND offerInquiryRequest) OR "EnVisionResponse version"   | xmlkv | stats range(_time) as duration by clientid

The clientid is a field in the XML body of the raw data. I used the xmlkv command to break up the XML into fields where I want to group by the clientid field. Is this the right way to approach this?

Thanks in Advance

Re: Why am I unable to group by an xmlkv field?

somesoni2 — Tue, 16 Aug 2016 19:52:24 GMT

It depends. How does your raw data looks like? Is it a pure xml OR you've some additional text?

Re: Why am I unable to group by an xmlkv field?

riotto — Tue, 16 Aug 2016 19:57:43 GMT

it's pure xml

Re: Why am I unable to group by an xmlkv field?

JDukeSplunk — Tue, 16 Aug 2016 20:04:53 GMT

I just tried the very same thing with one of my xmlkv searches with the same syntax as you show an it worked.

Maybe rename the field and see if that gives it some magic?

|rename clientid AS ClientID | stats range(_time) as duration by ClientId

Or possibly the field is a string, not a number so stats can't help it.

|eval ClientID=tonumber(clientid,10) |stats range(_time) as duration by ClientID

Re: Why am I unable to group by an xmlkv field?

riotto — Tue, 16 Aug 2016 20:09:55 GMT

"Or possibly the field is a string, not a number so stats can't help it."

It is a string, but I'm not sure what you mean by your comment that stats can't help it.

Re: Why am I unable to group by an xmlkv field?

somesoni2 — Tue, 16 Aug 2016 20:40:06 GMT

If it's a pure xml and if you've setup proper search time field extraction for xml data, then you'd see your fields on the left hand side field bar.
http://docs.splunk.com/Documentation/Splunk/6.4.2/Knowledge/Createandmaintainsearch-timefieldextractionsthroughconfigurationfiles#Setting_KV_MODE_for_search-time_data

Could you just run your base search (before xmlkv) in Smart/Verbose mode and check if you get the field clientid or not. Remember that field names are always case sensitive in all commands, so check that as well.

May be you can post a sample xml raw event so that we can check what the field name would look like.

Re: Why am I unable to group by an xmlkv field?

vasanthmss — Tue, 16 Aug 2016 20:50:12 GMT

I guess you were missing something in your xmlkv .. run the below search and look for the field client ip

index="personalizedoffer" earliest="08/16/2016:00:00:00" (XML_INPUT_LOGGER AND offerInquiryRequest) OR "EnVisionResponse version"   | xmlkv | table _time, clientid

If clientid field available use the below search to look for the chars,

index="personalizedoffer" earliest="08/16/2016:00:00:00" (XML_INPUT_LOGGER AND offerInquiryRequest) OR "EnVisionResponse version"   | xmlkv | table _time, clientid| eval length=length(clientid)

Suppose the clientid field is null for some events use fillnull and try below

 index="personalizedoffer" earliest="08/16/2016:00:00:00" (XML_INPUT_LOGGER AND offerInquiryRequest) OR "EnVisionResponse version"   | xmlkv |fillnull value=0 clientid | stats range(_time) as duration by clientid

Other aspects to debug, In-case the clientid field has some empty char then try to trim it and use.

My guess is xmlkv is not extracting the field you were refereeing in the search / doesn't have any values , Hope this will help you.

Re: Why am I unable to group by an xmlkv field?

riotto — Wed, 17 Aug 2016 13:49:08 GMT

If I do this

index="personalizedoffer" earliest="08/16/2016:00:00:00" (XML_INPUT_LOGGER AND offerInquiryRequest) OR "EnVisionResponse version"   | xmlkv

the clientid shows up as an "interesting field" - If I select it for display in "Table" mode, it displays correctly.

when I use your search:

index="personalizedoffer" earliest="08/16/2016:00:00:00" (XML_INPUT_LOGGER AND offerInquiryRequest) OR "EnVisionResponse version"   | xmlkv | table _time, clientid

shows an empty field for the clientid when I search

Each event has a clientid field in it and it is populated.

Why does it display when selected, but not display in the ...| table _time clientid?

Re: Why am I unable to group by an xmlkv field?

pasokkum — Wed, 17 Aug 2016 14:06:55 GMT

Try with "transaction" command for grouping

index="personalizedoffer" earliest="08/16/2016:00:00:00" (XML_INPUT_LOGGER AND offerInquiryRequest) OR "EnVisionResponse version"   | xmlkv | transaction clientid | table duration, clientid

Re: Why am I unable to group by an xmlkv field?

riotto — Wed, 17 Aug 2016 14:54:45 GMT

It's clientId, not clientid. That was the problem - I guess I need new glasses

Re: Why am I unable to group by an xmlkv field?

JDukeSplunk — Fri, 19 Aug 2016 04:45:26 GMT

If you were trying to perform some sort of math function to it. avg() sum() etc. Anyway glad your eyesight improved.