topic Re: How to convert multivalue fields with corresponding value in another field to column-value pair? in Splunk Search

How to convert multivalue fields with corresponding value in another field to column-value pair?

lindbergh_calde — Fri, 07 Apr 2017 00:03:07 GMT

Hi All,

We have recently configured the Splunk Add-on for Microsoft Cloud Services to pull o365 logs into Splunk. For the most part the field extractions are good, except for the extractions when a new OneDrive/Office 365 group is added. When a new group is added following is the part raw event i have can see in Splunk from the JSON pull

 ExtendedProperties:    [   [-] 
    {   [-] 
     Name:   DisplayName    
     Value:  [] => [Test Group];    
    }   
    {   [-] 
     Name:   IsPublic   
     Value:  [] => [False];     
    }   
    {   [-] 
     Name:   Mail   
     Value:  [] => [testgroup@microsoft.com];   
    }   
    {   [-]

The field extraction in Splunk show up as field - ExtendedProperties.Name and has values: DisplayName, IsPublic Mail and
field - ExtendedProperties.Value and has values: [] => [Test Group]; [] => [False]; [] => [testgroup@microsoft.com];.

I managed to use the eval{ExtendedProperties.Name}=ExtendedProperties.Value and when displayed in a table format the results appeared as the following all in one event.

However, i want to display the data in the following format, any ideas how to do this?

Re: How to convert multivalue fields with corresponding value in another field to column-value pair?

woodcock — Fri, 07 Apr 2017 03:29:08 GMT

Use mvzip to bind the pairs of values together and then use your eval {field_key_name} = field_value_name trick after doing mvexpand.

Re: How to convert multivalue fields with corresponding value in another field to column-value pair?

lindbergh_calde — Fri, 07 Apr 2017 04:06:44 GMT

Hi Greg,

Thanks for you response.

This is the search i ran

index=o365 Operation="Add group." | eval names='ExtendedProperties{}.Name', vals='ExtendedProperties{}.Value' | eval temp=mvzip(names,vals) | eval {names}=vals | mvexpand temp

can't seem to get the output i want it. Should i modify this search somehow?

Re: How to convert multivalue fields with corresponding value in another field to column-value pair?

woodcock — Fri, 07 Apr 2017 15:37:23 GMT

Like this:

| makeresults count=3
| streamstats count AS serial
| eval ExtendedProperties.Name="DisplayName IsPublic Mail"
| makemv ExtendedProperties.Name
| eval ExtendedProperties.Value=case(
   (serial=1), "Test Group::False::testgroup@microsoft.com",
   (serial=2), "Group1::True::group1@microsoft.com",
   (serial=3), "Group2::True::group2@microsoft.com")
| makemv delim="::" ExtendedProperties.Value
| fields - _time

| rename COMMENT AS "Everything above is faking your data; everyting below is your solution"

| eval KVP=mvzip('ExtendedProperties.Name', 'ExtendedProperties.Value', "=")
| table KVP serial
| mvexpand KVP
| rex field=KVP "^(?<KVP_key>[^=]+)=(?<KVP_value>[^=]+)$"
| eval {KVP_key}=KVP_value
| fields - KVP* 
| stats values(*) AS * BY serial

Re: How to convert multivalue fields with corresponding value in another field to column-value pair?

woodcock — Fri, 07 Apr 2017 15:37:48 GMT

See other answer.

Re: How to convert multivalue fields with corresponding value in another field to column-value pair?

lindbergh_calde — Sun, 09 Apr 2017 22:58:44 GMT

Thanks Greg

That worked brilliantly.