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

[chadman]

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?

[woodcock]

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

[chadman]

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"

[chadman]

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

[woodcock]

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.

[chadman]

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

[chadman]

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

[woodcock]

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)

[chadman]

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

[woodcock]

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.

[chadman]

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

[chadman]

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

[somesoni2]

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

[chadman]

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.

[javiergn]

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

[javiergn]

Use the isNull and null() functions instead:

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

[somesoni2]

Try like this

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