Splunk Enterprise

Is there any way to simplify this query?

rberman
Path Finder

I have a data field categ_hierarchy in the format of a series of up to 8 category IDs joined by ">>". For example:

categ_id1>>categ_id2>>categ_id3>>...>>categ_id8 

Category id 1 is required but category ids 2 through 8 are optional.  Category Ids are strings without the '>' cahracter in them. They might have whitespace that I want to trim from the beginning or end. 

Here are 2 examples:

simulink/index>>simulink/simulink-environment>>simulink/programmatic-modeling

support/parallel-221>>parallel-computing/index

I want to lookup each category id and get back the associated category name for each category id and then reconstruct the category names into a similarly formated path: 

categ_name1>>categ_name2>>categ_name3>>...>>categ_name8 

Here are the two examples constructed from the lookup results

Simulink >> Simulink Environment Fundamentals >> Programmatic Model Editing

Parallel Computing >> Parallel Computing Toolbox

Is there any way to simplify my SPL from this? 

| rex field=category_hierarchy "(?<categ_id1>[^>]+)(>>(?<categ_id2>[^>]+))?(>>(?<categ_id3>[^>]+))?(>>(?<categ_id4>[^>]+))?(>>(?<categ_id5>[^>]+))?(>>(?<categ_id6>[^>]+))?(>>(?<categ_id7>[^>]+))?(>>(?<categ_id8>[^>]+))?"
| eval categ_id1=trim(categ_id1), categ_id2=trim(categ_id2), categ_id3=trim(categ_id3), categ_id4=trim(categ_id4), categ_id5=trim(categ_id5), categ_id6=trim(categ_id6), categ_id7=trim(categ_id7), categ_id8=trim(categ_id8)
| lookup category_lookup category_id AS categ_id1 OUTPUTNEW category_name AS categ_name1
| lookup category_lookup category_id AS categ_id2 OUTPUTNEW category_name AS categ_name2
| lookup category_lookup category_id AS categ_id3 OUTPUTNEW category_name AS categ_name3
| lookup category_lookup category_id AS categ_id4 OUTPUTNEW category_name AS categ_name4
| lookup category_lookup category_id AS categ_id5 OUTPUTNEW category_name AS categ_name5
| lookup category_lookup category_id AS categ_id6 OUTPUTNEW category_name AS categ_name6
| lookup category_lookup category_id AS categ_id7 OUTPUTNEW category_name AS categ_name7
| lookup category_lookup category_id AS categ_id8 OUTPUTNEW category_name AS categ_name8
| eval category_name_hierarchy=categ_name1
| eval category_name_hierarchy=if(isnull(categ_name2), category_name_hierarchy, category_name_hierarchy." >> ".categ_name2)
| eval category_name_hierarchy=if(isnull(categ_name3), category_name_hierarchy, category_name_hierarchy." >> ".categ_name3)
| eval category_name_hierarchy=if(isnull(categ_name4), category_name_hierarchy, category_name_hierarchy." >> ".categ_name4)
| eval category_name_hierarchy=if(isnull(categ_name5), category_name_hierarchy, category_name_hierarchy." >> ".categ_name5)
| eval category_name_hierarchy=if(isnull(categ_name6), category_name_hierarchy, category_name_hierarchy." >> ".categ_name6)
| eval category_name_hierarchy=if(isnull(categ_name7), category_name_hierarchy, category_name_hierarchy." >> ".categ_name7)
| eval category_name_hierarchy=if(isnull(categ_name8), category_name_hierarchy, category_name_hierarchy." >> ".categ_name8)
| table category_hierarchy, category_name_hierarchy

I know I could split the category_hierachy field by the ">>" delimeter but I don't know how to lookup each of the category Ids in the resulting multivalue field. 

Any help would be appreciated!!

Thanks,

Rena

Labels (1)
Tags (3)
1 Solution

johnhuang
Motivator

Something like this:

 

 

| rex field=category_hierarchy "(?<categ_id1>[^>]+)(>>(?<categ_id2>[^>]+))?(>>(?<categ_id3>[^>]+))?(>>(?<categ_id4>[^>]+))?(>>(?<categ_id5>[^>]+))?(>>(?<categ_id6>[^>]+))?(>>(?<categ_id7>[^>]+))?(>>(?<categ_id8>[^>]+))?"

| foreach categ_id* [
	| eval output_name=null() 
	| eval "<<FIELD>>"=TRIM(<<FIELD>>)
	| lookup category_lookup category_id AS "<<FIELD>>" OUTPUT category_name AS output_name 
	| eval category_name_hierarchy=mvappend(category_name_hierarchy, output_name)
]
| eval category_name_hierarchy=MVJOIN(category_name_hierarchy, " >> ")

 

View solution in original post

johnhuang
Motivator

Something like this:

 

 

| rex field=category_hierarchy "(?<categ_id1>[^>]+)(>>(?<categ_id2>[^>]+))?(>>(?<categ_id3>[^>]+))?(>>(?<categ_id4>[^>]+))?(>>(?<categ_id5>[^>]+))?(>>(?<categ_id6>[^>]+))?(>>(?<categ_id7>[^>]+))?(>>(?<categ_id8>[^>]+))?"

| foreach categ_id* [
	| eval output_name=null() 
	| eval "<<FIELD>>"=TRIM(<<FIELD>>)
	| lookup category_lookup category_id AS "<<FIELD>>" OUTPUT category_name AS output_name 
	| eval category_name_hierarchy=mvappend(category_name_hierarchy, output_name)
]
| eval category_name_hierarchy=MVJOIN(category_name_hierarchy, " >> ")

 

rberman
Path Finder

Thank you so much!! I had looked into foreach but I guess I didn't know how to use it properly. I really appreciate the help!!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...