Splunk Search

If column is missing then eval

jiaqya
Builder

if a field is missing in output, what is the query to eval another field to create this missing field.

below query can do it,

|eval missing=anothercolumn.

but to run this query , i need to run it only when the "missing" column is missing.
what is the logic to use..

Labels (1)
Tags (1)
0 Karma
1 Solution

memarshall63
Communicator

You can use 'if' and 'isnull' to identify whether the field exists, and if not replace it with another field.

| makeresults 
| eval there = "NOTNULL" 
| eval NEWFIELD = if(isnull(notthere),"FIELD IS NULL", "FIELD IS AVAIL") 
| eval NEWFIELD2 = if(isnull(there),"FIELD IS NULL", "FIELD IS AVAIL")

or

| makeresults 
| eval there = "NOTNULL" 
| eval newfield = "NEW"
| eval NEWFIELD = if(isnull(notthere),"FIELD IS NULL", "FIELD IS AVAIL") 
| eval NEWFIELD2 = if(isnull(there),newfield, there)

View solution in original post

memarshall63
Communicator

You can use 'if' and 'isnull' to identify whether the field exists, and if not replace it with another field.

| makeresults 
| eval there = "NOTNULL" 
| eval NEWFIELD = if(isnull(notthere),"FIELD IS NULL", "FIELD IS AVAIL") 
| eval NEWFIELD2 = if(isnull(there),"FIELD IS NULL", "FIELD IS AVAIL")

or

| makeresults 
| eval there = "NOTNULL" 
| eval newfield = "NEW"
| eval NEWFIELD = if(isnull(notthere),"FIELD IS NULL", "FIELD IS AVAIL") 
| eval NEWFIELD2 = if(isnull(there),newfield, there)

jiaqya
Builder

This works good, thanks, can i ask for another help.

the eval missing=newcolumn.

the new column is the last column in the table which im evaling with missing.
how do i eval the last column to be missing..

0 Karma

jiaqya
Builder

simplifying this even further,.

if column "missing" is missing, then eval it with 12th column( this wont change )

to identify "missing" is actually missing, i am using your above code... of if and isnull

0 Karma

memarshall63
Communicator

That's a tougher question.

The only way I'd know how to do that is to leave the values in a string, split it, and then use mvindex to index the 12th value in the split.

| makeresults 
| eval message = "this,is,the,fourth,fifth,sixth,value" 
| eval new_mvfield = split(message,",") 
| eval FOURTH = mvindex(new_mvfield,3)
| eval FIFTH = mvindex(new_mvfield,4)
| eval LAST = mvindex(new_mvfield,-1)
0 Karma

jiaqya
Builder

thanks, luckily my column names are months, ie last 12 months, so my last column as of today is 'Apr 2020' followed by the 'Missing' column.

so i got the 'Apr 2020' using eval as below, but when i eval it with "missing" , it prints the actual value.
how to treat the value to be a column during eval on 2nd line...

|eval ThisMonth=strftime(relative_time(now(), "-0d"), "%b %Y")
| eval Missing = if(isnull(Missing),ThisMonth, Missing)
|fields - ThisMonth

output below :

Apr 2020 Missing
99.62 Apr 2020
99.37 Apr 2020
99.16 Apr 2020
99.42 Apr 2020

basically want the values of Apr 2020 to be under Missing, like below.

Apr 2020 Missing
99.62 99.62
99.37 99.37
99.16 99.16
99.42 99.42

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...