topic How to extract and aggregate by extracted fields in Splunk Search

How to extract and aggregate by extracted fields

ashriram — Thu, 24 Jun 2021 09:06:30 GMT

I have the data that looks like this
user, ip, (metrics kv pairs)

---- sample results for search --
user=user1,ip=10.10.10.10,key1=10,key2=30
user=user2,ip=10.10.10.10,key1=5,key3=30
user=user1,ip=10.10.10.12,key2=10,key3=30,key4=2,key5=14,key6=4
user=user1,ip=10.10.10.10,key5=22
-------------

How do I pull out the metrics - key1- key6 and aggregate by the metrics ?
say if i wanted a pie chart with all totals of all the keys for a given IP/ user (say IP and username are dashboard input tokens)

Re: How to extract and aggregate by extracted fields

ITWhisperer — Thu, 24 Jun 2021 09:33:06 GMT

Re: How to extract and aggregate by extracted fields

kamlesh_vaghela — Thu, 24 Jun 2021 10:18:50 GMT

@ashriram

Can you please try this?

YOUR_SEARCH | rex field=_raw "user=(?<user>[^,]*),ip=(?<ip>[^,]*),(?<keys>[^\n]*)" | rex field=keys "key\d=(?<k>[^\n|,]*)" max_match=0 | stats sum(k) as key_sum by ip user

you can changes as per your requirement like

| stats sum(k) as key_sum by ip

| stats sum(k) as key_sum by user

My Sample Search :

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: How to extract and aggregate by extracted fields

ashriram — Thu, 24 Jun 2021 17:48:50 GMT

This helped me solve my use case

Had to make a few tweaks, because my keys were Not in the format `key{number}`

`max_match=0` is what really solved it for me

this is how my solution looked:

SEARCH | rex field=_raw "user=(?<user>[^,]*),ip=(?<ip>[^,]*),(?<metrics>[^\"]+)"" | rex field=metrics "(?<metric>\w+)=(?<m_count>\d+),?" max_match=0 | stats sum(m_count) as total_counts by metric, ip user

thanks a lot @kamlesh_vaghela

Re: How to extract and aggregate by extracted fields

kamlesh_vaghela — Fri, 25 Jun 2021 07:24:58 GMT

Cool.

Glad to help you. Please accept the answer to close this question.