topic Re: How to edit my eval syntax to create a new field for null values? in Splunk Search

How to edit my eval syntax to create a new field for null values?

chadman — Mon, 11 Jul 2016 16:42:31 GMT

I'm trying to create a new field for some null values. I tried this, but it still shows the null value.

eval Reboot=if(internal_time=null AND careers_time=null,100,null)

am I missing something?

Re: How to edit my eval syntax to create a new field for null values?

somesoni2 — Mon, 11 Jul 2016 16:45:22 GMT

Try like this

eval Reboot=if(isnull(internal_time) AND isnull(careers_time),100,null())

Re: How to edit my eval syntax to create a new field for null values?

javiergn — Mon, 11 Jul 2016 16:46:33 GMT

Use the isNull and null() functions instead:

eval Reboot=if(isNull(internal_time) AND isNull(careers_time),100, null())

Re: How to edit my eval syntax to create a new field for null values?

chadman — Mon, 11 Jul 2016 16:52:23 GMT

Thanks guys, but that is not working. So I might not have given you guys all the info. When I said null, I mean there is no event. My Splunk data is grabbed from a csv file that is updated every min. While a machine is powered down or rebooting that log does not get updated. When I chart info it just show's blanks on a bar. In this case I want to show a value of 100 when that happens.

Re: How to edit my eval syntax to create a new field for null values?

javiergn — Mon, 11 Jul 2016 17:02:04 GMT

I guess you want to use the fillnull command for that.

See the link:

https://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Fillnull

Or maybe eval with coalesce

Re: How to edit my eval syntax to create a new field for null values?

somesoni2 — Mon, 11 Jul 2016 17:05:05 GMT

In that case use the filldown method OR streamstats to fill the values for the periods when no data is available.
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Filldown
https://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Streamstats

Re: How to edit my eval syntax to create a new field for null values?

woodcock — Tue, 12 Jul 2016 00:58:03 GMT

It really, really, really, really, really, really, really helps to see your entire search. I assume that you are using timechart so all you need to do is call fillnull for the last field on the before the next pipe.

Re: How to edit my eval syntax to create a new field for null values?

chadman — Tue, 12 Jul 2016 12:50:33 GMT

Sorry, here is my entire search.

sourcetype="search1" host=host1  | eval Offline=if(internal_time="NA" AND careers_time="NA",500,NA)  | timechart avg(internal_time) as "Internal Ping Time" avg(careers_time) as "External Ping Time" avg(Offline)

I still cant get the sections with no data to fill

Re: How to edit my eval syntax to create a new field for null values?

chadman — Tue, 12 Jul 2016 13:01:29 GMT

This looked like the right format, but still not seeing data.

sourcetype="search1" host=host1  | eval Offline=if(internal_time="NA" AND careers_time="NA",500,NA) | fillnull value="nodata" internal_time careers_time |  eval NoPower=if(internal_time="nodata" AND careers_time="nodata",250,NA) |timechart avg(internal_time) as "Internal Ping Time" avg(careers_time) as "External Ping Time" avg(Offline) as Offline avg(NoPower) as NoPower

Re: How to edit my eval syntax to create a new field for null values?

woodcock — Tue, 12 Jul 2016 13:33:20 GMT

OK, like this:

 sourcetype="search1" host=host1 | timechart avg(internal_time) AS "Internal Ping Time" avg(careers_time) AS "External Ping Time" avg(Offline)
| foreach "* *" [ eval <<FIELD>>=if((<<FIELD>>=0), 100, $<<FIELD>>$) ]

Or maybe this:

 sourcetype="search1" host=host1 | timechart avg(internal_time) AS "Internal Ping Time" avg(careers_time) AS "External Ping Time" avg(Offline)
| eval Reboot=if("Internal Ping Time"=0 AND "External Ping Time"=0,100, 0)

Re: How to edit my eval syntax to create a new field for null values?

chadman — Tue, 12 Jul 2016 14:18:42 GMT

the foreach comes up with an unknown search command error. The other search gives an error for the compares unless I put the 0 in quotes. It will do the search, but I still don't have a value for the missing data

Re: How to edit my eval syntax to create a new field for null values?

woodcock — Tue, 12 Jul 2016 16:35:15 GMT

There is something VERY big missing in the explanation of your situation so rather than give you a direction solution (many have tried and failed), I will give you the understanding that you need to craft your own.

Here is how timechart works. It will create an event for EVERY aggregate time value (in your case, since you did not tell us your timepicker values and you did not specify a span= value, we cannot know the spacing particulars) whether or not there is a value for anything else. If there is not a value for any time slot, it will simply not have a value (null). Take this base search:

| gentimes [|noop|stats count AS end|eval end=now() | eval start = end - 4*60*60*24
| eval start=strftime(start, "%m/%d/%Y") | eval end=strftime(end, "%m/%d/%Y")
| format "" "" "" "" "" "" | return $search]
| eval _time = starttime | table _time
| streamstats count AS X

It gives events like this:

_time         X
2016-07-08  1
2016-07-09  2
2016-07-10  3
2016-07-11  4

Now that we have sample events that anybody can use on any system, we can add a basic timechart with additional eval like this:

| timechart span=1d avg(X) AS avgX avg(Y) AS avgY
| eval avgZ = (avgX + avgY)/2

When we run for the Last 7 days then we get this:

_time          avgX          avgY
2016-07-05
2016-07-06
2016-07-07
2016-07-08  1.000000
2016-07-09  2.000000
2016-07-10  3.000000
2016-07-11  4.000000
2016-07-12

All the desired fields are broken: avgX had gaps, avgY has no data and avgZ doesn't exist at all.
We can force any null values to obtain any specific value we like with the fillnull command.
If we insert it between the last 2 lines like this:

| timechart span=1d avg(X) AS avgX avg(Y) AS avgY
| fillnull value=0 avgX avgY | rename Comment AS "<-------THIS LINE IS INSERTED"
| eval avgZ = (avgX + avgY)/2

Then we get all fields with data:

_time       avgX  avgY  avgZ
2016-07-05  0   0   0
2016-07-06  0   0   0
2016-07-07  0   0   0
2016-07-08  1   0   0.5
2016-07-09  2   0   1
2016-07-10  3   0   1.5
2016-07-11  4   0   2
2016-07-12  0   0   0

Re: How to edit my eval syntax to create a new field for null values?

chadman — Tue, 12 Jul 2016 19:30:57 GMT

Thanks for that! I think I'm close. I think I have the fillnull working now. I then use that data to try to create my "Power Off" field. It's only showing 0's. I can see the "Internal Ping Time" and "External Ping Time" both = "md"

sourcetype="search1" host=host1  | eval Offline=if(internal_time="NA" AND careers_time="NA",500,"NA")  | timechart span=1m avg(internal_time) as "Internal Ping Time" avg(careers_time) as "External Ping Time" avg(Offline) as Offline | fillnull value="md" "Internal Ping Time" "External Ping Time" | eval "Powered Off"=if("Internal Ping Time"="md" AND "External Ping Time"="md",250,0) | fields "Internal Ping Time" "External Ping Time" Offline "Powered Off"

Re: How to edit my eval syntax to create a new field for null values?

chadman — Tue, 12 Jul 2016 19:47:38 GMT

So I have this:

sourcetype=search1 host=host1  | eval Offline=if(internal_time="NA" AND careers_time="NA",500,"NA")  | timechart span=1m avg(internal_time) as "Internal Ping Time" avg(careers_time) as "External Ping Time" avg(Offline) as Offline | fillnull value="md" "Internal Ping Time" "External Ping Time" Offline | eval "Powered Off"=if("Internal Ping Time"="md" AND "External Ping Time"="md" AND Offline="md",400,0) | fields "Internal Ping Time" "External Ping Time" Offline "Powered Off"

I get output that looks like:

1 7/7/16 12:00:00.000 PM md md 500.000000 0
2 7/7/16 12:01:00.000 PM md md 500.000000 0
3 7/7/16 12:02:00.000 PM md md 500.000000 0
4 7/7/16 12:03:00.000 PM md md md 0 Here is where I think I should see 250
5 7/7/16 12:04:00.000 PM md md md 0 Here is where I think I should see 250
6 7/7/16 12:05:00.000 PM md 48.000000 md 0
7 7/7/16 12:06:00.000 PM md 73.000000 md 0
8 7/7/16 12:07:00.000 PM md 48.000000 md 0
9 7/7/16 12:08:00.000 PM md 80.000000 md 0
10 7/7/16 12:09:00.000 PM 78.000000 md md 0
11 7/7/16 12:10:00.000 PM md md md 0

Re: How to edit my eval syntax to create a new field for null values?

woodcock — Wed, 13 Jul 2016 16:13:24 GMT

You need to add the field names (header line on top) and format the spacing so we can make sense of your output. Be sure to lead with a blank line and have 4 spaces in front of every line. In any case, there really isn't anything more to say. Just work your way slowly through it.

Re: How to edit my eval syntax to create a new field for null values?

chadman — Wed, 13 Jul 2016 16:58:42 GMT

It seems I can make the changes and fill in the null values. I just can't get my eval to read those values to form the "Powered Off" field. It just shows all 0's. It's like it won't read the null values I have filled.

Internal Ping Time External Ping Time Offline Powered Off _time
md md 500 0 2016-07-07T12:00:00.000-0500
md md 500 0 2016-07-07T12:01:00.000-0500
md md 500 0 2016-07-07T12:02:00.000-0500
md md md 0 2016-07-07T12:03:00.000-0500
md md md 0 2016-07-07T12:04:00.000-0500
md 48 md 0 2016-07-07T12:05:00.000-0500
md 73 md 0 2016-07-07T12:06:00.000-0500

Re: How to edit my eval syntax to create a new field for null values?

chadman — Wed, 13 Jul 2016 16:59:21 GMT

The bold lines should not have a 0 and should show 400