Iterate over all values of a multivalue field?

[PrinceOfEval]

I'm trying to pull a bunch of logs, group them by user account, and then compare aspects of IP addresses involved per user. I use stats count and stats list to format the data so that a single row exists for each user account, and all the IPs associated with that user account are stored in a multivalued field. I want to iterate through the multivalued field and compare IP1 with IP2, IP2 with IP3, etc for all the values. The main issue is that there will be an indeterminate number of values in each multivalued field, so I can't just split into new columns using "| eval newcol1=mvindex(oldcol, 0) | eval newcol2=mvindex(oldcol, 1)", etc.

Not sure if I'm overcomplicating this or just missing something.

Thoughts?

[martin_mueller · Accepted solution]

Alternatively, avoid creating that multivalue field in the first place. You said you have some combination of usernames, IPs, and | stats count list()... so something like this?

base search | stats count list(IP) as IPs by user

Consider using this instead:

base search | stats count by user IP

That'll give you a table like this:

user      IP count
a    1.1.1.1     3
a    1.1.1.2     4
b    1.1.1.3     1

Using that, you can now copy over field values to the next row like this:

... | streamstats current=f global=f window=1 last(IP) as last_IP by user

user      IP count  last_IP
a    1.1.1.1     3
a    1.1.1.2     4 1.1.1.1
b    1.1.1.3     1

Now you can do your comparisons using those two fields. Note only one value is filled due to my small sample size.

...whether that works depends on what you actually want to do. However, in general there's often not a lot of good coming from using list() or values() and then doing more processing on those fields.

[wpreston]

How about something like this? In the search below, FieldA is the field containing all the multivalue fields you want to compare:

your base search 
| eval FieldAPlusOne=mvindex(FieldA,1,-1) 
| eval TempField=mvzip(FieldA,FieldAPlusOne) 
| mvexpand TempField 
| rex field=TempField "(?<LeftVal>[^,]+),(?<RightVal>.*)"
| eval Comparison=LeftVal-RightVal
| table _time LeftVal RightVal Comparison

The search basically creates a new field made up of pairs of data from FieldA (first and second values, second and third values, third and fourth values, etc), creates a new event for each pair, expands the pair of values into different fields, then performs a comparison.

[aweitzman]

Here's a splunk-hacky way to separate the things you want just enough for you to be able to hopefully do what you want:

search-here | eval numIPs=mvcount(IPs) | eval indexval=mvrange(0,numIPs,1) | mvexpand indexval | eval compfield=someeval(mvindex(IPs,indexval))

mvrange gets you a multivalued field with just numbers in a range, which you can use as indexes. mvexpand breaks that out so that for every set of IPs, you have a number of rows equal to the number of values in the set, and a column that just has an index value in it.

Hope that helps.

[jrodman]

I've never found a good way for this. multivalue fields work fine when they're just a bag of values to feed into various commands, but when you need to actually consider all the values for a particular field I have no good recipe. Unless I'm missing something there are definitely missing convenience utilities here.