Splunk Search

Why am I unable to group by an xmlkv field?

Path Finder

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

0 Karma
1 Solution

Path Finder

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

View solution in original post

0 Karma

Path Finder

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

View solution in original post

0 Karma

Path Finder

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

Motivator

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.

Path Finder

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?

0 Karma

Builder

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

Path Finder

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

0 Karma

Builder

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

it's pure xml

0 Karma

SplunkTrust
SplunkTrust

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

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.

0 Karma