Splunk Search

How can I create a "null" or "blank" response in a field while converting strings into a new string value?

jh007
New Member

I am not sure how to approach what I am attempting to do. In short, I have a field that contains some specific strings that I intend to convert into a new string value inside a new field. For all the other strings in the first field that do not match, I want to provide a null or "blank" response in the new field.

For example:

original field values // New field value
planetrainsautomobiles // modes of transportation
applesPeachesblueberries // types of fruit
random garbage I don't want // [blank]

I know if I use the eval command in conjunction with replace I can change any string of text to what I want. What I can't figure out is how to "null" all the garbage I don't want to show in the new field when I display it in the table.

Any help would be greatly appreciated.

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

It takes just two steps. Note that you don't have to pull the entire word, just enough to identify it, so you don't have to worry about plural or singular.

 | rex field=origfield "(?i)(?<newfield>plane|train|automobile|apple|peach|blueberr)" max_match=0
 | eval newfield=coalesce(mvdedup(lower(newfield)),"")
 | rex field=newfield mode=sed "s/plane|train|automobile/mode of transportation/g s/apple|peach|blueberr/types of fruit/g"  

Here's where it can get fun.

Given a csv file in this format | table myvalue myreplacement, this code will build the first and second rexes

| makeresults 
| eval mydata="plane,modes of transportation!!!!peach unicorn,modes of transportation!!!!train,modes of transportation!!!!automobile,modes of transportation!!!!horseless carriage,modes of transportation!!!!apple,types of fruit!!!!peach,types of fruit!!!!blueberr,types of fruit!!!!goji berr,types of fruit"
| makemv delim="!!!!" mydata 
| mvexpand mydata 
| makemv delim="," mydata
| eval myvalue=mvindex(mydata,0)
| eval myreplacement=mvindex(mydata,1)
| fields - mydata
| rename COMMENT as "the above just enters test data."


| rename COMMENT as "build the first rex with the same test data"
| rename COMMENT as "sort descending so that shorter versions of same characters are last"
| table myvalue
| sort 0 - myvalue
| rename COMMENT as "hide spaces so we can restore them later"
| rex field=myvalue mode=sed "s/ /!!!!/g"
| rename COMMENT as "format the return value and then restore the spaces"
| format "(?i)(?<newfield>" "" "" "" "|" ")"
| rex field=search mode=sed "s/myvalue=|[ \"]//g s/^\(/\"(/g s/\)$/)\"/g s/!!!!/ /g"


| rename COMMENT as "build the second rex with the same test data"
| rename COMMENT as "sort descending so that shorter versions of same characters are last"
| table myvalue myreplacement
| sort 0 - myvalue
| stats list(myvalue) as myvalue by myreplacement
| eval YYYYYYYY=mvjoin(myvalue,"|")
| rename myreplacement as ZZZZZZZZ
| table YYYYYYYY ZZZZZZZZ 
| format "" "" "" "" "" ""
| rex field=search mode=sed "s/YYYYYYYY=/s\//g s/\/\"/\//g s/\"  ZZZZZZZZ=\"/\//g s/\"/\/g /g"

So the final code looks like this

 | rex field=origfield [| inputcsv mychange.csv 
     | rename COMMENT as "sort descending so that shorter versions of same characters are last" 
     | table myvalue 
     | sort 0 - myvalue 
     | rename COMMENT as "hide spaces so we can restore them later" 
     | rex field=myvalue mode=sed "s/ /!!!!/g" 
     | rename COMMENT as "format the return value and re them later" 
     | format "(?i)(?<newfield>" "" "" "" "|" ")" 
     | rex field=search mode=sed "s/myvalue=|[ \"]//g s/^\(/\"(/g s/\)$/)\"/g s/!!!!/ /g"]  max_match=0
 | eval newfield=coalesce(mvdedup(lower(newfield)),"")
 | rex field=newfield mode=sed [| inputcsv mychange.csv 
     | rename COMMENT as "sort descending so that shorter versions of same characters are last" 
     | table myvalue myreplacement 
     | sort 0 - myvalue 
     | stats list(myvalue) as myvalue by myreplacement 
     | eval YYYYYYYY=mvjoin(myvalue,"|") 
     | rename myreplacement as ZZZZZZZZ 
     | table YYYYYYYY ZZZZZZZZ 
     | format "" "" "" "" "" "" 
     | rex field=search mode=sed "s/YYYYYYYY=/s\//g s/\/\"/\//g s/\"  ZZZZZZZZ=\"/\//g s/\"/\/g /g" ]

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

It takes just two steps. Note that you don't have to pull the entire word, just enough to identify it, so you don't have to worry about plural or singular.

 | rex field=origfield "(?i)(?<newfield>plane|train|automobile|apple|peach|blueberr)" max_match=0
 | eval newfield=coalesce(mvdedup(lower(newfield)),"")
 | rex field=newfield mode=sed "s/plane|train|automobile/mode of transportation/g s/apple|peach|blueberr/types of fruit/g"  

Here's where it can get fun.

Given a csv file in this format | table myvalue myreplacement, this code will build the first and second rexes

| makeresults 
| eval mydata="plane,modes of transportation!!!!peach unicorn,modes of transportation!!!!train,modes of transportation!!!!automobile,modes of transportation!!!!horseless carriage,modes of transportation!!!!apple,types of fruit!!!!peach,types of fruit!!!!blueberr,types of fruit!!!!goji berr,types of fruit"
| makemv delim="!!!!" mydata 
| mvexpand mydata 
| makemv delim="," mydata
| eval myvalue=mvindex(mydata,0)
| eval myreplacement=mvindex(mydata,1)
| fields - mydata
| rename COMMENT as "the above just enters test data."


| rename COMMENT as "build the first rex with the same test data"
| rename COMMENT as "sort descending so that shorter versions of same characters are last"
| table myvalue
| sort 0 - myvalue
| rename COMMENT as "hide spaces so we can restore them later"
| rex field=myvalue mode=sed "s/ /!!!!/g"
| rename COMMENT as "format the return value and then restore the spaces"
| format "(?i)(?<newfield>" "" "" "" "|" ")"
| rex field=search mode=sed "s/myvalue=|[ \"]//g s/^\(/\"(/g s/\)$/)\"/g s/!!!!/ /g"


| rename COMMENT as "build the second rex with the same test data"
| rename COMMENT as "sort descending so that shorter versions of same characters are last"
| table myvalue myreplacement
| sort 0 - myvalue
| stats list(myvalue) as myvalue by myreplacement
| eval YYYYYYYY=mvjoin(myvalue,"|")
| rename myreplacement as ZZZZZZZZ
| table YYYYYYYY ZZZZZZZZ 
| format "" "" "" "" "" ""
| rex field=search mode=sed "s/YYYYYYYY=/s\//g s/\/\"/\//g s/\"  ZZZZZZZZ=\"/\//g s/\"/\/g /g"

So the final code looks like this

 | rex field=origfield [| inputcsv mychange.csv 
     | rename COMMENT as "sort descending so that shorter versions of same characters are last" 
     | table myvalue 
     | sort 0 - myvalue 
     | rename COMMENT as "hide spaces so we can restore them later" 
     | rex field=myvalue mode=sed "s/ /!!!!/g" 
     | rename COMMENT as "format the return value and re them later" 
     | format "(?i)(?<newfield>" "" "" "" "|" ")" 
     | rex field=search mode=sed "s/myvalue=|[ \"]//g s/^\(/\"(/g s/\)$/)\"/g s/!!!!/ /g"]  max_match=0
 | eval newfield=coalesce(mvdedup(lower(newfield)),"")
 | rex field=newfield mode=sed [| inputcsv mychange.csv 
     | rename COMMENT as "sort descending so that shorter versions of same characters are last" 
     | table myvalue myreplacement 
     | sort 0 - myvalue 
     | stats list(myvalue) as myvalue by myreplacement 
     | eval YYYYYYYY=mvjoin(myvalue,"|") 
     | rename myreplacement as ZZZZZZZZ 
     | table YYYYYYYY ZZZZZZZZ 
     | format "" "" "" "" "" "" 
     | rex field=search mode=sed "s/YYYYYYYY=/s\//g s/\/\"/\//g s/\"  ZZZZZZZZ=\"/\//g s/\"/\/g /g" ]
0 Karma

jh007
New Member

So the command syntax seems to work, but I am running into an issue with handling backslashes in my rex statements. Here's my example:

SAM_ALIASDOMAINS\Builtin\Aliases\00000220C\C:\Windows\System32\lsass.exe ...

How do I handle these?

0 Karma

jh007
New Member

It took 3 slashes oddly enough on both the rex and rex sed statements.

Thank you for your help!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@jh007 - see why I hate those? Glad you got it figured out.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@jh007 - oh, I hate those. Sometimes you have to escape them once, giving you two slashes, and sometimes you have to escape them twice, giving you four slashes for a single one. (And what's after them seems to matter sometimes. Gak!) Unfortunately, it depends on exactly where you are putting in the rex - inline, in a panel, inside a map command, or in a .conf, so I always have to play with it a bit to be certain.

Start with four slashes and if that doesn't work, cut down to 2.

If it's in a plain search, then use makeresults to create a simple example of what you are trying to UNDO, and then you can test a simple rex against it until it works, and repeat whatever worked across the full mask.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If I understand correctly, you want something like this.

... | eval NewFieldValue=case(OriginalFieldValue="planetrainsautomobiles", "modes of transportation", OriginalFieldValue="applesPeachesblueberries", "types of fruit", 1==1, "") | table OriginalFieldValue NewFieldValue
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...