topic Re: mvexpand multiple multi-value fields? in Splunk Search

mvexpand multiple multi-value fields: How do I turn my three multi-value fields into tuples?

nathanh42 — Sat, 24 Sep 2022 03:36:18 GMT

I have a query that extracts useful info from a storage system report.

rex "quota list --verbose (?<fs>[A-Z0-9_]+) " | rex max_match=1000 "ViVol: (?<vivol>(?!user)[A-Za-z0-9]+)\nUsage\s+:\s+(?<usage>[0-9. A-Za-z]+)\n\s*Limit\s+:\s+(?<limit>[0-9A-Z. ]+)" | table fs, vivol, usage, limit

There is a single line at the start of the report with the filesystem which I extract as the "fs" field. Then there are several volume descriptions containing separate lines for the volume, usage and limit.

This query produces a single-value field for "fs" then three multi-value fields "vivol", "usage" and "limit". e.g.

   fs                       vivol                 usage                    limit
   FIRST_FS                 VOL_ABC               100                      300
                            VOL_XYZ               320                      800
                            VOL_123               50                       150

When I export this to Excel (using CSV) the multi-value fields are all within a single cell. I want them on separate rows. If I use mvexpand I get the unexpected behaviour that it will properly expand one field but leave the others unexpanded. If I expand all three fields they lose correlation so I get rows that are mixed-up.

   FIRST_FS                 VOL_123              320                       300

How do I turn my three multi-value fields into tuples? I want to keep them together so the first row in "vivol" matches the first rows in "usage" and "limit". Bear in mind there are many "fs" events (about 100 of them).

Re: mvexpand multiple multi-value fields?

nathanh42 — Fri, 03 Jun 2011 05:47:11 GMT

Here's an example of the storage system report I'm starting with, if that helps.

Content-Transfer-Encoding: 7bit
Scheduled job console-context --evs 1 quota list --verbose FIRST_FS produced the following output:
Type            : Explicit
Target          : ViVol: VOL_ABC
Usage           : 100 GB
  Limit         : 500 GB (Hard)
Last modified   : 2010-12-19 04:56:50.834383000+00:00
Type            : Explicit
Target          : ViVol: VOL_123
Usage           : 609 GB
  Limit         : 3 TB (Hard)
Last modified   : 2010-12-21 04:04:23.757073000+00:00

Re: mvexpand multiple multi-value fields?

ahall_splunk — Sat, 17 Mar 2012 01:50:44 GMT

I just had the same issue.

Create a single field with all the eventual fields you want, so you have a single MV, then use mvexpand to create the multiple entries, then do another parse on the (now single-) value to extract the three fields.

Re: mvexpand multiple multi-value fields?

jlord — Fri, 27 Jun 2014 01:39:52 GMT

I ran into the same issue with two multi-valued fields, and arrived at a different solution - make a copy of the field to preserve the order for an mvfind, then use mvexpand, look up the value in the added field, lookup each field that was NOT expanded, then drop the added field. It would look something like:

This solution worked better for me as I was using a stats list(x) list(y) and needed to keep the values correlated.

Re: mvexpand multiple multi-value fields?

djfang — Thu, 07 May 2015 16:45:42 GMT

Just ran into a similar issue, glad I found your solution. Thanks!

Re: mvexpand multiple multi-value fields?

mrobichaud_splu — Thu, 04 Jun 2015 18:37:51 GMT

Use mvzip, makemv and then reset the fields based on index.

First, mvzip the multi-values into a new field:

| eval reading=mvzip(vivol, usage)    // create multi-value field for reading
| eval reading=mvzip(reading, limit)  // add the third field

At this point you'll have a multi-value field called reading. Here's an example of a field value (a list of four items):

"VOL_ABC,100,300",  "VOL_XYZ,320,800", "VOL_123, 50,150", "VOL_FOO, 80,120"

Expand the field and restore the values:

| mvexpand reading                    // separate multi-value into into separate events
| makemv reading delim=","            // convert the reading into a multi-value
| eval vivol=mvindex(reading, 0)      // set vivol to the first value of reading
| eval usage=mvindex(reading, 1)      // set usage to the second value of reading
| eval limit=mvindex(reading, -1)     // set limit to the last value of reading

Re: mvexpand multiple multi-value fields?

ksextonmacb — Tue, 29 Sep 2020 07:30:06 GMT

Very helpful, thanks. I ended up with a completed search that did exactly what I wanted using the above stuff.

Re: mvexpand multiple multi-value fields?

joshualarkins — Thu, 18 Feb 2016 19:22:57 GMT

this worked for some JSON data I had where I needed to preserve relationships among elements of an array

Re: mvexpand multiple multi-value fields?

landen99 — Fri, 16 Sep 2016 23:34:57 GMT

Separate the data into lines (events):

| rex max_match=0 "(?<line>[^\)]+\)\n\N+)" | mvexpand line | table line

[https://regex101.com/r/qN6tG2/1]
Next, do your extractions:

| rex field=line "quota list --verbose (?<fs>[A-Z0-9_]+) " | rex field=line max_match=1000 "ViVol: (?<vivol>(?!user)[A-Za-z0-9]+)\nUsage\s+:\s+(?<usage>[0-9. A-Za-z]+)\n\s*Limit\s+:\s+(?<limit>[0-9A-Z. ]+)" | table fs, vivol, usage, limit

Use the SPL command filldown:

| filldown fs

to get:

    fs                       vivol                 usage                    limit
FIRST_FS                 VOL_ABC               100                      300
FIRST_FS                 VOL_XYZ               320                      800
FIRST_FS                 VOL_123               50                       150

then take it from there.

Re: mvexpand multiple multi-value fields?

boozsplunk — Fri, 24 Feb 2017 19:46:27 GMT

I downvoted this post because .

Re: mvexpand multiple multi-value fields?

sk314 — Thu, 07 Sep 2017 06:56:16 GMT

This is so great. I am writing this comment (and upvoting) AFTER searching for this answer and using it for the third time. Quite ungrateful. 😕

Re: mvexpand multiple multi-value fields?

mrobichaud_splu — Thu, 07 Sep 2017 15:16:22 GMT

Thanks @sk314. To be fair, this question was left unanswered for four years and 35 hours. Some improvements have been made to the docs since this answer, but this example is still better, IMO.

Re: mvexpand multiple multi-value fields?

DalJeanis — Fri, 06 Oct 2017 18:52:21 GMT

Assuming that all the mv fields MUST have the same number of items...

| eval myFan=mvrange(0,mvcount(vivol))
| mvexpand myFan
| eval vivol=mvindex(vivol,myFan)
| eval usage=mvindex(usage,myFan)
| eval limit=mvindex(limit,myFan)

Re: mvexpand multiple multi-value fields?

vikfnu — Tue, 31 Jul 2018 23:10:07 GMT

Seriously this is a great help

Re: mvexpand multiple multi-value fields?

aagro — Wed, 23 Jan 2019 13:08:43 GMT

Hi DalJeanis,
your solution is ingenious.

Thanks so much!

Antonio

Re: mvexpand multiple multi-value fields?

anjalisaini — Wed, 06 Feb 2019 13:02:37 GMT

Thanks , It helped me so much 🙂 .

Re: mvexpand multiple multi-value fields?

dmanojbaba — Fri, 08 Feb 2019 23:52:14 GMT

Here is another solution to this problem:

https://answers.splunk.com/answers/724138/

Re: mvexpand multiple multi-value fields?

dmanojbaba — Fri, 08 Feb 2019 23:53:33 GMT

Here is another answer to this question:

https://answers.splunk.com/answers/724138/

Please upvote, if it helps.

Re: mvexpand multiple multi-value fields?

D2SI — Thu, 02 May 2019 15:30:55 GMT

Thanks a lot!

Re: mvexpand multiple multi-value fields?

wlombard — Wed, 30 Sep 2020 03:43:20 GMT

Updated regex a bit to select the values as per the example:

| rex field=line "quota list --verbose (?[A-Z0-9_]+) "
| rex field=line max_match=1000 "ViVol: (?(?!user)[A-Za-z0-9_]+)\nUsage\s+:\s+(?[0-9.]+)[A-Za-z\s\n]+Limit\s+:\s+(?[0-9]+)[A-Za-z\s+()]+"
| table fs, vivol, usage, limit