How to map extracted keys to values and separate them?

piyushpandey — Wed, 08 Feb 2023 22:22:48 GMT

I have logs which contain parts like:
.. { "profilesCount" : { "120000" : 100 , "120001" : 500 , "110105" : 200 , "totalProfilesCount" : 1057}} ..
here the key is accountId and value is the number of profiles in it.

when I use max_count=0 in rex and extract these values I get:
accountId=[12000000, 12000001, 11001005] and pCount=[100, 500, 200] for this example event.

Since these accountIds are not mapped to their corresponding pCount when I visualize them I get

accountId	pCount
12000000	100 500 200
12000001	100 500 200
11001005	100 500 200

how can I map them correctly and show in a table form?

This was my search query:
search <search_logic> | rex max_match=0 "\"(?<account>\d{8})\" : (?<pCount>\d+)"] | stats values(pCount) by account

Thanks in advance

Re: How to map extracted keys to values and separate them?

bowesmana — Wed, 08 Feb 2023 23:22:00 GMT

If your data is JSON, then spath or json_extract can extract that, e.g. something like

| eval p=json_extract(_raw, "profilesCount") | spath input=p | fields - _raw _time totalProfilesCount p | transpose

or you can use foreach to make a field of the account, e.g.

| rex max_match=0 "\"(?<account>\d{8})\" : (?<pCount>\d+)" | foreach 0 1 2 3 4 5 [ eval n=mvindex(account,<<FIELD>>), {n}=mvindex(pCount,<<FIELD>>) | fields - n]

or you can use zip/expand/extract

| eval zip=mvzip(account, pCount, "=") | fields - account pCount | mvexpand zip | rex field=zip "(?<account>\d{8})=(?<pCount>\d+)"

Hopefully one of these approaches will get you where you want to get to

topic Re: How to map extracted keys to values and separate them? in Splunk Enterprise

How to map extracted keys to values and separate them?

Re: How to map extracted keys to values and separate them?