Splunk Search
Highlighted

question on makemv and mvexpand

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?

Highlighted

Re: question on makemv and mvexpand

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