How to display one to one mapping between fields in stats command

neerajs_81 — Mon, 03 Mar 2025 12:14:37 GMT

Hi there, how can i use stats command to one to one mapping between fields . I have tried "list" function and "values" function both but results are not expected.
Example: we are consolidating data from 2 indexes and both indexes have same fields of interests ( user, src_ip)
Base query:

index=okta or index=network | iplocation (src_ip) |stats values(src_ip) values(deviceName) values(City) values(Country) by user, index

Results:
We get something like this

user	index	src_ip	DeviceName	Country
John_smith	okta	10.0.0.1 192.178.2.24	laptop01	USA
John_smith	network	198.20.0.14 64.214.71.89 64.214.71.90 71.29.100.90	laptop01 laptop02 server01 My-CloudPC	USA

Expected results:
How to map which src_ip is coming from which Devicename? We want to align the Devicename in same sequence as per the src_ip ?

If i use list instead of values in my stats, it shows duplicates like this for src_ip and deviceName. Even doing a |dedup src_ip is not helping

Hope clear.

Re: How to display one to one mapping between fields in stats command

richgalloway — Thu, 06 Mar 2025 15:23:45 GMT

The values and list functions display results in lexicographic order and destroy any potential relationship among the fields. One solution is use mvzip to combine fields, group the results, then unzip the fields.

index=okta or index=network | iplocation (src_ip) | eval tuple = mvzip(src_ip, mvzip(deviceName, mvzip(City, Country))) | stats values(tuple) by user, index | eval fields = split(tuple, ",") | eval src_ip = mvindex(fields, 0), deviceName=mvindex(fields,1), City=mvindex(fields, 2), Country=mvindex(fields,3)

A better approach might be to perform the iplocation command after stats.

index=okta or index=network | stats values(src_ip) as src_ip by user, index | mvexpand src_ip | iplocation (src_ip)

topic Re: How to display one to one mapping between fields in stats command in Splunk Search

How to display one to one mapping between fields in stats command

Re: How to display one to one mapping between fields in stats command