Splunk Search

Why am I unable to group by an xmlkv field?

riotto
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

riotto
Path Finder

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

View solution in original post

0 Karma

riotto
Path Finder

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

0 Karma

pasokkum
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

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

V

riotto
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

JDukeSplunk
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

riotto
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

JDukeSplunk
Builder

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

0 Karma

somesoni2
Revered Legend

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

0 Karma

riotto
Path Finder

it's pure xml

0 Karma

somesoni2
Revered Legend

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
Get Updates on the Splunk Community!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...