Splunk Search

How to add values of multiple variable, where the number of variable differs in different events

harish_ka
Communicator

i have a field in my log as "BookCount 10 /BookCount"
if a Library pass contains more than one members then the field repeats based on the number of members.
For Ex: If a Library pass has 1 members, then my log returns 1 bookcount values as "BookCount 10 /BookCount"
If a Library pass has 3 members, then my log returns three bookcount values as below,
"BookCount 10 /BookCount" "BookCount 50 /BookCount" "BookCount 40 /BookCount"
I want to get the sum of bookcount.
I am using rex "(openTag)BookCount(?CountOfBook.*)(closeTag)BookCount"|stats list(CountOfBook) as "TotalCount".
I am getting only the first value.

Can anyone suggest me a better query to implement this????

Tags (2)
0 Karma
1 Solution

lguinn2
Legend

If the data is formatted as XML, you could try the xmlkv command:

yoursearchhere
| xmlkv

rex is only going to give you the first value, because your regular expression only finds the first value.
You could try this

yoursearchhere
| rex max_match=0 "\<BookCount\>\s*(?<CountOfBook>\d+)\s*\<\/BookCount\>"
| mvexpand CountOfBook
| stats sum(CountofBook) as TotalBooks

This should extract multiple values of BookCount; you will end up with a multi-valued field.

View solution in original post

lguinn2
Legend

If the data is formatted as XML, you could try the xmlkv command:

yoursearchhere
| xmlkv

rex is only going to give you the first value, because your regular expression only finds the first value.
You could try this

yoursearchhere
| rex max_match=0 "\<BookCount\>\s*(?<CountOfBook>\d+)\s*\<\/BookCount\>"
| mvexpand CountOfBook
| stats sum(CountofBook) as TotalBooks

This should extract multiple values of BookCount; you will end up with a multi-valued field.

harish_ka
Communicator

So how to add this values?
i need the total value of BookCount

0 Karma

harish_ka
Communicator

Thanks Lguinn.
But this regex didnt work for me.
Can you explain how can i use xmlkv for getting this total of CountOfBook

0 Karma

lguinn2
Legend

xmlkv won't create the total, but it should extract the fields.

I edited the regular expression above - maybe it will work now.

I also added the command to sum the values in a multi-valued field.

0 Karma

harish_ka
Communicator

How can i get the sum of bookcount if the BookCount tags are not appeared one after another in my log??
LIke below

"BookCount 10 /BookCount" "BookName ABC /BookName" ......."BookCount 50 /BookCount" "BookName XYZ /BookName"......"BookCount 40 /BookCount"

0 Karma

harish_ka
Communicator

It worked...
Thank You so much...
:)

0 Karma

lguinn2
Legend

I assume that the format is actually XML, like this:

<BookCount>10</BookCount>

or

<BookCount>10</BookCount><BookCount>50</BookCount><BookCount>40</BookCount>

with no quotation marks?

0 Karma

harish_ka
Communicator

"BookCount 10 /BookCount" is a tag. I was not able to use <> in this text box.
openTag(BookCount)-value-CloseTag(BookCount)

0 Karma

harish_ka
Communicator

Can anyone help me on this????

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