Splunk Search

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

shakSplunk
Path Finder

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:

EnvironmentApplicationCatridgeTypeCartridgeVersion
DEVA-1UserAlpha1.1
DEVA-2ProductBeta1.2
UATA-1UserAlpha1.2
SVPA-1UserAlpha1.4
SVPA-1UserSigma1.5
SVPA-2ProductBeta1.2
SVPA-3SystemGamma1.5

 

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

CartridgeTypeCartridgeDEV:A-1DEV:A-2
UserAlpha1.1 
ProductBeta 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:

CartridgeTypeCartridgeSVP:A-1SVP:A-2SVP:A-3
UserAlpha1.4  
UserSigma1.5  
ProductBeta 1.2 
SystemGamma  1.5

 

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

 

Thank you so much for any help!

Labels (2)
0 Karma
1 Solution

cmtri
Explorer

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

View solution in original post

0 Karma

cmtri
Explorer

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

0 Karma

shakSplunk
Path Finder

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}

0 Karma

cmtri
Explorer

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

0 Karma

shakSplunk
Path Finder

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

0 Karma

cmtri
Explorer

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

0 Karma

shakSplunk
Path Finder

Still is showing only CartridgeType and Cartridge fields. 

0 Karma

cmtri
Explorer

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:

 

CartridgeTypeCartridgeDEV:A-1
UserAlpha1.1 
0 Karma

shakSplunk
Path Finder

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!

0 Karma

cmtri
Explorer

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

View solution in original post

0 Karma

shakSplunk
Path Finder

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?

0 Karma

cmtri
Explorer

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.

0 Karma

shakSplunk
Path Finder

too fast, cheers!

0 Karma

shakSplunk
Path Finder

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!