- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to edit my eval syntax to create a new field for null values?
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?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The bold lines should not have a 0 and should show 400
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use the isNull and null() functions instead:
eval Reboot=if(isNull(internal_time) AND isNull(careers_time),100, null())
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try like this
eval Reboot=if(isnull(internal_time) AND isnull(careers_time),100,null())