Unable to create time series chart in Splunk dashboard

[santosh_hb]

Hi All, I need help in creating time chart for the following request:

I have a field by name field.status that will be '2' when the server is down and will be '0' when the server is up

For ex: the first host (Host1) is down from 16/04/2017 10:00:00 AM to 16/04/2017 10:10 AM (i.e. check.status = 2) and came up (check.status=0) at 16/04/2017 10:11 AM for the host Host1
Similarly, the second host (Host2) is down from 16/04/2017 10:05 AM to 16/04/2017 10:30 AM (i.e. check.status = 2) and came up (check.status=0) at 16/04/2017 10:31 AM

Now, I want to plot a time series line chart that should display the status of each host separately. Please help me in plotting this time chart

My Search query is something like this:

search query |  check.status=2 AND occurrences>2 | dedup id 
| join id [ search query |search check.status=2 
                |stats min(timestamp) as first_time by id,client.name,client.address 
                | eval "First Seen Time"=strftime(first_time,"%Y/%m/%d %H:%M:%S") ] 
| join id [search query | search check.status=0 
               |stats min(timestamp) as recov_time by id,client.name,client.address 
               |eval "Recover Time"=strftime(recov_time,"%Y/%m/%d %H:%M:%S") ] 
|  eval total_time=recov_time-first_time 
| rename total_time as "Total Time (in secs.)" client.cluster as "Cluster Name" client.name as "Host Name" client.address as "Host Address" 
| table "Cluster Name","Host Name", "Host Address", "First Seen Time","Recover Time","Total Time (in secs.)",id, occurrences

Let me know if you need additional details.

[lguinn2]

Okay, I fixed the search (I hope) and added stuff to plot a line when the host is down and nothing when the host is up. It is pretty complicated, but this is the only way I could think of to do it: create the table much like you did, then generate a timeline, map the times onto the timeline and finally format the data so it can be displayed with a line chart visualization. Note that I trimmed out a lot of things that you can display in a table, but not on a line chart. So the search part is easier here:

search query (check.status = 2 OR check.status=0)
| rename check.status as check_status
| stats count as occurrences min(timestamp) as minTime by id, client.name, client.address check_status
| where occurrences > 2
| eval tag = id . ":" . client.name . ":" . client.address
| fields - id, client.name, client.address
| eval first_time=if(check_status==2, minTime, null())
| eval recov_time=if(check_status==0, minTime, null())
| stats first(first_time) as first_time first(recov_time) as recov_time by tag
| streamstats count as host_number | eval _time = first_time
|  append  [ gentimes start=-7 increment=1h | sort 500 -endtime 
    | transpose 500 header_field=starttime include_empty=true | head 1 ] 
| foreach 1*  [ eval curPeriod = 1 . <<MATCHSTR>> 
    | eval <<FIELD>> = if(first_time <= curPeriod, host_num, null())
    | eval <<FIELD>> = if('<<FIELD>>' ==host_num AND curPeriod <= recov_time , '<<FIELD>>' , null()) ]
| fields - column first next host_num recov_time first_time occurrences check_status
| table tag *
| transpose 1000 header_field=tag 
| rename column as _time
| eval _time = strftime(_time,"%x %X")

I really feel like there should be an easier way, but darned if I can think of it. Maybe someone else will give a brilliant answer.

If you want to do a lot of nice graphics, I recommend that you download and examine the Splunk 6.x Dashboard Examples app; it's great. There is also the Machine Learning Toolkit and even an app for a Gantt chart. (I've never used the Gantt chart, but it might work well for this.)

[santosh_hb]

Hi, sorry for the delay in reply. The above query is not giving the desired result. I am looking for a timechart that should have events coming from each events within the First_time and recover_time

[lguinn2]

If all of your "search queries" are the same (or even substantially similar), this could be a very inefficient search.

If they are all the same:

search query (check.status = 2 OR check.status=0)
| stats count as occurrences min(timestamp) as minTime by id, client.name, client.address check.status
| where occurrences > 2
| eval first_time=if(check.status==2,minTime,null())
| eval recov_time=if(check.status==0,minTime,null())
| stats first(first_time) as first_time first(recov_time) as recov_time by id, client.name, client.address check.status
| eval total_time = recov_time - first_time
| rename total_time as "Total Time (in secs.)" client.cluster as "Cluster Name" client.name as "Host Name" client.address as "Host Address" 
| eval "Recover Time"=strftime(recov_time,"%Y/%m/%d %H:%M:%S") 
| eval "First Seen Time"=strftime(first_time,"%Y/%m/%d %H:%M:%S")
| table "Cluster Name","Host Name", "Host Address", "First Seen Time","Recover Time","Total Time (in secs.)",id, occurrences

[santosh_hb]

search query | stats first(first_time) as first_time first(recov_time) as recov_time by id, client.name, client.address check.status.
The above query doesn't work and the output is empty.

[lguinn2]

The problem is that a period (.) is not really valid in a field name, so it confuses the eval. Sorry, I forgot about that. So the lines should really be the way you did it.

[lguinn2]

I am struggling a bit with the timeline chart - working on it...

[santosh_hb]

Thanks for providing an alternate search. But, I am getting below error when I execute the above query
Error in 'eval' command: Typechecking failed. The '==' operator received different types.

The above error is coming from the below search query:
eval first_time=if(check.status==2,minTime,null())

Please note that the data is in JSON format and check.status is an JSON object.

Also, it will he helpful if you can help me with the time chart for the above query as well.

[niketn]

@santosh_hb, please try the following:

| eval first_time=if(check.status=="2",minTime,null())
| eval recov_time=if(check.status=="0",minTime,null())

2 and 0 values should be in double quotes.

[santosh_hb]

Thanks, it works. But, I already tried it by adding ' quotes and it also worked.

| eval first_time=if('check.status'==2,minTime,null())
| eval recov_time=if('check.status'==0,minTime,null())