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!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...