Splunk Search

question on makemv and mvexpand

sg5258
Explorer

i have search query that seperate multivalue and expand them into various result. It work for entry that has data but will ignore those empty change which i also want it to display ..
Here is the sample data

user    Mail_diff     GMail_diff     Opt_diff
A        NULL           NULL            NULL
B        X to Y         Z to 1          4 to 5
C        NULL            NULL           this to that

then my search query is following

 index="post-user" NOT "POST*" | 
    eval new = if (GMail_diff != "NULL", "GMail_diff--".GMail_diff,"") |
    eval new2 = if (Mail_diff != "NULL", "Mail_diff--".Mail_diff,"") |
    eval new3 = if (Opt_diff != "NULL", "Opt_diff--".Opt_diff,"") |
    eval Changed = new."/".new2."/".new3."/ |makemv Changed delim="/" |mvexpand Changed|eval z= split(Changed,"to")| eval before = mvindex(z,0) | eval after =mvindex(z,-1)
|table before,after,user

This query search the index post-user, i use eval to select field that is not NULL and put them into different variable first, then if it is empty then ignore. Then i concatenate them with "/" and make them into multiple value again. Then i expand then so that each _diff become new row. The last part i use split function to differentiate between before and after.

My search result is following:

before                  after                user
Mail_diff -- X           Y                    B
Mail_diff -- Z           1                    B
Mail_diff -- 4           5                    B
Opt_diff --  this        that                 C

I am unable to include user A!! .. which i need it as well.. ideally, is like that..

before                  after                user
                                              A
Mail_diff -- X           Y                    B
Mail_diff -- Z           1                    B
Mail_diff -- 4           5                    B
Opt_diff --  this        that                 C

Anyone, has any idea?

woodcock
Esteemed Legend

How about this instead:

index="post-user" NOT "POST*"
| replace "X" with "Y" in Mail_diff | replace "Z" with "1" in GMail_diff | replace "this" with "that" in Opt_diff | replace "4" with "5" in Opt_diff
| eval after=case(
Mail_diff="NULL", if(GMail_diff="NULL",Opt_diff,GMail_diff . ":::" . Opt_diff),
GMail_diff="NULL", if(Opt_diff="NULL",Mail_diff,Mail_diff . ":::" . Opt_diff),
Opt_diff="NULL", Mail_diff . ":::" . GMail_diff,
1==1, Mail_diff. ":::" . GMail_diff. ":::" . Opt_diff 
)| makemv delim=":::" after | mvexpand after | fields user after
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...