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

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...