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

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

jh007 — Mon, 18 Sep 2017 19:18:17 GMT

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.

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

richgalloway — Mon, 18 Sep 2017 19:40:39 GMT

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

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

DalJeanis — Mon, 18 Sep 2017 19:50:07 GMT

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" ]

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

jh007 — Mon, 18 Sep 2017 21:01:03 GMT

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?

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

DalJeanis — Tue, 19 Sep 2017 00:22:03 GMT

@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.

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

jh007 — Tue, 19 Sep 2017 12:36:43 GMT

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

Thank you for your help!

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

DalJeanis — Tue, 19 Sep 2017 14:26:19 GMT

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