Splunk Search

What data transform approach should I use with this? MV, tranpose, ...?

ChioNeng
Explorer
Im kinda newbie here in splunk. Whats the difference between multivalue and transpose command? how can i convert this input to this desired output? (see tables below) Is Multivalue or transpose command can manage this?

For a newbie like me those commands is kinda tricky. Many thanks in advance
 

input:

Field1Field2Country1-Jun1-Jul1-Aug1-Sep1-Oct
XABThailand2323433111
YCDSingapore232125222
ZEFPhilippines3156438423

 

output:

Field1Field2CountryMonthValue
XABThailand1-Jun23
XABThailand1-Jul2343
XABThailand1-Aug31
XABThailand1-Sep1
XABThailand1-Oct 1
YCDSingapore1-Jun2
YCDSingapore1-Jul321
YCDSingapore1-Aug2
YCDSingapore1-Sep52
YCDSingapore1-Oct 22
ZEFPhilippines1-Jun31
ZEFPhilippines1-Jul56
ZEFPhilippines1-Aug43
ZEFPhilippines1-Sep84
ZEFPhilippines1-Oct 23
0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults 
| eval _raw="Field1	Field2	Country	1_Jun	1_Jul	1_Aug	1_Sep	1_Oct
X	AB	Thailand	23	2343	31	1	1
Y	CD	Singapore	2	321	2	52	22
Z	EF	Philippines	31	56	43	84	23" 
| multikv 
| table Field1 Field2 Country 1_Jun 1_Jul 1_Aug 1_Sep 1_Oct 
| foreach 1_* 
    [ rename <<FIELD>> as 1-<<MATCHSTR>>] 
| rename COMMENT as "this is your sample. from here, the logic"    
| eval tmp=mvzip(Field1,mvzip(Field2,Country)) 
| fields - F* Country 
| untable tmp Month Value 
| rex field=tmp "(?<Field1>[^,]+),(?<Field2>[^,]+),(?<Country>.*)" 
| fields - tmp 
| eval Month=strptime(Month."2019","%d-%b%Y") 
| table Field* Country Month Value 
| fieldformat Month=strftime(Month,"%d-%b") 
| sort Field1 Month

mvexpand or tranpose is not need.

View solution in original post

0 Karma

to4kawa
Ultra Champion
| makeresults 
| eval _raw="Field1	Field2	Country	1_Jun	1_Jul	1_Aug	1_Sep	1_Oct
X	AB	Thailand	23	2343	31	1	1
Y	CD	Singapore	2	321	2	52	22
Z	EF	Philippines	31	56	43	84	23" 
| multikv 
| table Field1 Field2 Country 1_Jun 1_Jul 1_Aug 1_Sep 1_Oct 
| foreach 1_* 
    [ rename <<FIELD>> as 1-<<MATCHSTR>>] 
| rename COMMENT as "this is your sample. from here, the logic"    
| eval tmp=mvzip(Field1,mvzip(Field2,Country)) 
| fields - F* Country 
| untable tmp Month Value 
| rex field=tmp "(?<Field1>[^,]+),(?<Field2>[^,]+),(?<Country>.*)" 
| fields - tmp 
| eval Month=strptime(Month."2019","%d-%b%Y") 
| table Field* Country Month Value 
| fieldformat Month=strftime(Month,"%d-%b") 
| sort Field1 Month

mvexpand or tranpose is not need.

0 Karma

ChioNeng
Explorer

thanks a lot @to4kawa 🙂 

0 Karma
Get Updates on the Splunk Community!

CX Day is Coming!

Customer Experience (CX) Day is on October 7th!! We're so excited to bring back another day full of wonderful ...

Strengthen Your Future: A Look Back at Splunk 10 Innovations and .conf25 Highlights!

The Big One: Splunk 10 is Here!  The moment many of you have been waiting for has arrived! We are thrilled to ...

Now Offering the AI Assistant Usage Dashboard in Cloud Monitoring Console

Today, we’re excited to announce the release of a brand new AI assistant usage dashboard in Cloud Monitoring ...