How to extract and aggregate by extracted fields

[ashriram]

Hi 

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)

[kamlesh_vaghela · Accepted solution]

@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 :

| makeresults | eval raw="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", raw=split(raw,"|")|mvexpand raw|rename raw as _raw
| 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

Thanks
KV
▄︻̷̿┻̿═━一

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

[ashriram]

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 

[kamlesh_vaghela]

Cool.

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

KV

[ITWhisperer]

| extract
| stats sum(*) as * by ip user
| eval userip=user.":".ip
| fields - ip user
| table userip *