How to mvcombine results that already have OTHER Multivalue results without messing up the formatting?

[scknogas]

Okay, I think I'm losing my mind with trying to work with the formatting of multivalue outputs...

Let's say I have a query that returns a series of single value results in field1, each with a set of some (or none) multi-value results in field2 and field3... Like this example query...

index=test_query
     | stats count
     | eval field1="host1"
     | eval field2=mvrange(1,7,2)
     | table field1 field2 field3
     | append 
       [ search index=test_query 
       | stats count 
       | eval field1="host2" 
       | eval field3=mvrange(2,7,2) 
       | table field1 field2 field3]
     | append 
       [ search index=test_query 
       | stats count 
       | eval field1="host3" 
       | eval field3=mvrange(2,7,2) 
       | table field1 field2 field3]
| table field1 field2 field3

Which generate a table that looks like...

+========+========+========+
| field1 | field2 | field3 |
+========+========+========+
| host1  |    1   |        |
|        |    3   |        |
|        |    5   |        |
+--------+--------+--------+
| host2  |        |    2   |
|        |        |    4   |
|        |        |    6  |
+--------+--------+--------+
| host3  |        |    2   |
|        |        |    4   |
|        |        |    6  |
+--------+--------+--------+

NOTE: My actual search results are not just numbers in the multivalue fields but long filenames like /var/log/monkey/squirrel and /usr/local/bin/destro_tool. Thus formatting is critical for human readability.

So, what I'd like to do is combine the results where the field2 & field3 match, thus creating a multivalue version of field1. Easy! Just use mvcombine, with a query like...

index=test_query
     | stats count
     | eval field1="host1"
     | eval field2=mvrange(1,7,2)
     | table field1 field2 field3
     | append 
       [ search index=test_query 
       | stats count 
       | eval field1="host2" 
       | eval field3=mvrange(2,7,2) 
       | table field1 field2 field3]
     | append 
       [ search index=test_query 
       | stats count 
       | eval field1="host3" 
       | eval field3=mvrange(2,7,2) 
       | table field1 field2 field3]
| mvcombine field1 
| table field1 field2 field3

Which generate a table that looks like...

+========+========+========+
| field1 | field2 | field3 |
+========+========+========+
| host1  | 1 3 5  |        |
+--------+--------+--------+
| host2  |        |  2 4 6 |
| host3  |        |        |
+--------+--------+--------+

But, that has changed the formatting of field2 and field3, and what I REALLY need is to have a table that looks like...

+========+========+========+
| field1 | field2 | field3 |
+========+========+========+
| host1  |    1   |        |
|        |    3   |        |
|        |    5   |        |
+--------+--------+--------+
| host2  |        |    2   |
| host3  |        |    4   |
|        |        |    6  |
+--------+--------+--------+

So, how can I combine field1 into multivalue and still keep the formatting of field2 & field3 to be multivalue as well?

[woodcock]

It is not possible. It is possible to fake it, but it would not be wise because doing so violates the UI presentation design and would cause great confusion to anyone looking at your searches and results. The best that you can do is to do what you are doing but do it with optimal SPL.

| makeresults
| eval raw="host1,field1=3,5 host2,field2=2,4,6, host3,field2=2,4,6"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<host>[^,]+),(?<field>[^=]+)=(?<values>.*)$"
| makemv delim="," values
| eval {field} = values
| table host field1 field2
| stats values(*) AS * BY host

| rename COMMENT AS "Everything above generates sample event data; everything below is the best way to get what you already have"

| nomv field1
| nomv field2
| multireport
[ stats list(host) AS host BY field1 ]
[ stats list(host) AS host BY field2 ]

[somesoni2]

Will one of field2 and field3 always be null?

[scknogas]

Yes, the results can be "empty", but I "fix" that by doing...

 where ( isnotnull(field2) OR isnotnull (field3))
              | fillnull value="" field2, field3

[somesoni2]

I believe the workaround here would be to 1) make field2 and field3 non-multivalued field, 2) do mvcombine, 3) make field2 and field3 multivalued field again. I can try that implementing if you could share your full query. Since the values in actual search will be different from this test query, it'll be better to see original query.

[scknogas]

Are you thinking of using "nomv" and "makemv"?

As for my actual query, here is a sanitized version...

index IN ( index_to_search ) source="/var/log/tripwire.log" 
    "Total violations found: " AND NOT ("Total violations found: 0") ("Added:" OR "Modified:") 
| rex max_match=0 field=_raw "Modified:\n\"(?P<Modified>[^\"]+)" 
| rex max_match=0 field=_raw "Added:\n\"(?P<Added>[^\"]+)" 
| eval Modified = mvfilter(NOT match(Modified,"/dev/initctl") 
    AND NOT match(Modified,"/dev/log") 
    AND NOT match(Modified,"/dev/tty*") 
    AND NOT match(Modified,"/etc/rc.d/rc2.d") 
    AND NOT match(Modified,"/root") 
    AND NOT match(Modified,"/usr/lib64/python2.7/site-packages") 
    AND NOT match(Modified,"/var/lock/subsys") 
    AND NOT match(Modified,"/var/lock/subsys/network") 
    AND NOT match(Modified,"/var/www/conf/foo.conf") 
    AND NOT match(Modified,"/var/www/tmp")) 
| where ( isnotnull(Modified) OR isnotnull (Added)) 
| fillnull value="" Added, Modified 
| dedup index, host, Added, Modified 
| fields index, host, Added, Modified 
| table index, host, Added, Modified

It's looking at the tripwire output in the logs, to extract the added/modified/deleted changes, remove the known acceptable changes, and make the rest into a readable report.