topic Re: Building a table from single multidimensional input in Dashboards & Visualizations

Building a table from single multidimensional input

knielsen — Tue, 14 Nov 2017 08:03:37 GMT

Hello,

I am dealing with .csv input files that list cost/amount for various components per month. Format is this:

month,cost: ABC, amount: ABC, cost: DEF, amount: DEF, cost: FOO, amount: FOO
2017-10,123.02,42,44.44,20,99.99,36

Is there a way to put it into a table like this:

Component | cost    | amount
ABC       | 123.02  | 42
DEF       | 44.44   | 20
FOO       | 99.99   | 36

Regards,
Kai.

Re: Building a table from single multidimensional input

kamlesh_vaghela — Tue, 14 Nov 2017 08:50:18 GMT

Can you please try this?

| inputlookup my_csv_lookup | foreach "amount:*" [eval <<MATCHSTR>>='<<FIELD>>' ] | foreach "cost:*" [eval <<MATCHSTR>>='<<MATCHSTR>>'.",".'<<FIELD>>' ] | table month ABC DEF FOO | transpose | rename column as Component | search Component!="month" | eval amount = mvindex(split('row 1',","),0), cost = mvindex(split('row 1',","),1) | table Component cost amount

Thanks

Re: Building a table from single multidimensional input

knielsen — Tue, 14 Nov 2017 09:01:57 GMT

Thanks, that does exactly what I asked for with real data. Although now that leaves me to additional problems, I'll see what I can do about those.

Anyway, your solution is correct for me!

Re: Building a table from single multidimensional input

kamlesh_vaghela — Tue, 14 Nov 2017 09:14:01 GMT

can you please try this?

| inputlookup my_csv_lookup 
| foreach "amount:*" 
    [ eval <<MATCHSTR>>='<<FIELD>>' ] 
| foreach "cost:*" 
    [ eval <<MATCHSTR>>='<<MATCHSTR>>'.",".'<<FIELD>>' ] 
| fields - amount:* cost:* 
| eval Component = "ABC,".ABC."|DEF,".DEF."|FOO,".FOO 
| makemv Component delim="|" 
| fields month Component 
| mvexpand Component 
| eval amount = mvindex(split(Component,","),1), cost = mvindex(split(Component,","),2), Component = mvindex(split(Component,","),0) 
| table month Component cost amount

Thanks

Re: Building a table from single multidimensional input

knielsen — Tue, 14 Nov 2017 10:44:52 GMT

Works as well. I think I prefer your first solution though, makes dealing with different component names a tad bit easier.

There is no "Accept" Button to mark your solution as correct?

Regards,
Kai.

Re: Building a table from single multidimensional input

kamlesh_vaghela — Tue, 14 Nov 2017 11:27:48 GMT

Hi @knielsen

Kindly accept the answer and upvote my comment which helps you.

Happy Splunking