(Table) Add dynamically generated columns (from field values) onto pre-existing columns

[shakSplunk]

Hi all,

I'm trying to dynamically add columns to two fixed columns based on the environment value selected. For instance, this is the input data:

Environment	Application	CatridgeType	Cartridge	Version
DEV	A-1	User	Alpha	1.1
DEV	A-2	Product	Beta	1.2
UAT	A-1	User	Alpha	1.2
SVP	A-1	User	Alpha	1.4
SVP	A-1	User	Sigma	1.5
SVP	A-2	Product	Beta	1.2
SVP	A-3	System	Gamma	1.5

And I would like to create a table such as the following: 

CartridgeType	Cartridge	DEV:A-1	DEV:A-2
User	Alpha	1.1
Product	Beta		1.2

Some key things to note:

- The first two columns should stay constant, however depending on the environment value selected in the search (e.g. Environment="DEV"), the environment value should be combined with the 'Application' value to create another column, in which the values are the corresponding 'Version' value. The tricky party is making the fields after "Cartridge" dynamic, for instance, if Environment="SVP",  I would expect the following:

CartridgeType	Cartridge	SVP:A-1	SVP:A-2	SVP:A-3
User	Alpha	1.4
User	Sigma	1.5
Product	Beta		1.2
System	Gamma			1.5

Is this possible to do whilst making to only show the latest version value?

Thank you so much for any help!

[cmtri]

| eval {Environment}:{Application}='Version'

[shakSplunk]

Hi @cmtri 
Thanks for your answer but I'm still a little confused. The following doesn't seem to be working?

index="main" sourcetype="data" 
| eval {Environment}:{Application}="Version"
| table CartridgeType Cartridge {Environment}:{Application}

[cmtri]

Version should be surrounded in single-quotes (') rather than double-quotes (") to use the value of the Version field. You don't need these single-quotes, so you can remove them if you prefer. If you also table out and then eval the fields after they will automatically appear in the table, so the below should work:

index="main sourcetype="data"

| table CartridgeType, Cartridge

| eval {Environment}:{Application}='Version'

The second line is also valid like below (no quotes):

| eval {Environment}:{Application}=Version

[shakSplunk]

The table is only showing the CartridgeType, Cartridge fields however isn't showing the dynamic fields after it.

[cmtri]

Sorry, you need to bring the two fields you're using into the table, then drop them later to do it like that!

You can then use the fields command to remove them from the table.

index="main sourcetype="data"

| table CartridgeType, Cartridge, Environment, Application

| eval {Environment}:{Application}=Version

| fields - Environment, Application

[shakSplunk]

Still is showing only CartridgeType and Cartridge fields. 

[cmtri]

You can try the below to see a full search:

| makeresults 1
| eval CartridgeType="User"
| eval Cartridge="Alpha"
| eval Environment="DEV"
| eval Application="A-1"
| eval Version="1.1"
| table Environment, Application, CartridgeType, Cartridge, Version
| eval {Environment}:{Application}=Version
| fields - Environment, Application, Version

This should generate a table like below:

CartridgeType	Cartridge	DEV:A-1
User	Alpha	1.1

[shakSplunk]

Hi @cmtri Thought it was working, but there is slightly one more issue. 

This is what I entered, 

| makeresults | eval _raw="Environment,Application,CartridgeType,Cartridge,Version
SVP,A-1,User,Alpha,1.4
SVP,A-1,User,Sigma,1.5
SVP,A-2,Product,Beta,1.2
SVP,A-3,System,Gamma,1.5
SVP,A-2,User,Alpha,1.5" | multikv forceheader=1
| table Environment, Application, CartridgeType, Cartridge, Version
| eval {Environment}:{Application}=Version
| fields - Environment, Application, Version

Line 1 and 5 of the resulting table should be combined together - meaning that since it has the same cartridgetype and cartridge, it should be on one line.  However, the result is producing it as seperate rows. Also, thanks for your help so far!

[cmtri · Accepted solution]

This stats values command should do it!

| makeresults
| eval _raw="Environment,Application,CartridgeType,Cartridge,Version
SVP,A-1,User,Alpha,1.4
SVP,A-1,User,Sigma,1.5
SVP,A-2,Product,Beta,1.2
SVP,A-3,System,Gamma,1.5
SVP,A-2,User,Alpha,1.5"
| multikv forceheader=1
| table Environment, Application, CartridgeType, Cartridge, Version
| eval {Environment}:{Application}=Version
| fields - Environment, Application, Version
| stats values by CartridgeType, Cartridge

[shakSplunk]

thank you, we are so close! @cmtri 

Your solution solves the line issue however now each of the dynamic columns have 'values' at the front: E.g.
'values(SVP:A-1)'

. Is there a way to remove the brackets and the 'values' prefix, so it goes back to being 'SVP:A-1' and so on for all the dynamically created columns?

[cmtri]

And use this to make the columns the original names rather than the values(abc) result:

| rename values(*) as *

Thanks! If this solves it please mark as answered.

[shakSplunk]

too fast, cheers!

[shakSplunk]

This seems to work! Seems like the issue was not including version in the table clause.