Splunk Search

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

lindbergh_calde
Explorer

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.
alt text

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

lindbergh_calde
Explorer

Thanks Greg

That worked brilliantly.

0 Karma

woodcock
Esteemed Legend

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

0 Karma

lindbergh_calde
Explorer

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?

0 Karma

woodcock
Esteemed Legend

See other answer.

0 Karma
Get Updates on the Splunk Community!

This Week's Community Digest - Splunk Community Happenings [9.26.22]

Get the latest news and updates from the Splunk Community here! Upcoming User Group Events! &#x1f44f; Check ...

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...