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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...