topic Re: Regarding implementation of Calendar visualization in Splunk Search

Regarding implementation of Calendar visualization

accgarima — Fri, 17 Feb 2017 05:44:02 GMT

I have a requirement where I have four fields :
1. AverageValue (of a month for some parameter A)
2. ActualValue (on each date for that month for the parameter A)
3. DeviationValue (AverageValue-ActualValue/Average Value --> on each date for the month)---> this field will be color coded based on the devation percentage
4. Each Date for the month with ActualValue

For example say for the month of february the average value is 5:

AverageValue ActualValue DeviationValue(%) Date

5 8 -60 02/06/2017
5 7 -40 02/08/2017
5 9 -80 02/10/2017

I want to display the Deviation value with the color coding in the Calendar Visualization for each month (in this example say February).

I know we need to use time chart command , but some how I am not getting the output . Please help.

Re: Regarding implementation of Calendar visualization

niketn — Fri, 17 Feb 2017 10:43:24 GMT

Can you give timechart command you are using are you computing AverageValue ActualValue and Deviation value using the final timechart command or using stats/eval prior to that?

In case you already have computed average Actual and Deviation prior to the timechart then based on the number of buckets per day for a month you might have to use values() or min() or max() or any other suitable command.

For the deviation percent field, make sure it is numeric field to be plotted.

Are you using Calendar Heat Map custom visualization or using JS/CSS extension?

Re: Regarding implementation of Calendar visualization

duraij — Wed, 12 Jul 2017 21:13:51 GMT

Hi I have the same issue I am trying to display my output in calendar Visualization and I am using timechart command but I dont get any output eventhough I get results for the same query when I use chart or table command.Please help.
My query looks like this "index=""|eval BankCTime=strptime(BankClose, "%Y-%m-%d %H:%M:%S") | convert ctime(BankCTime),timeformat="%H"|eval GCTime=strptime(GC, "%Y-%m-%d %H:%M:%S") |convert ctime(GCTime),timeformat="%H"|fields COBDATE BankCTime GCTime|timechart span=1d count(GCTime) by COBDATE"
Data:
COBDATE BankCTime GCTime
20160701 14.15 13.12
20160702 13.10 15.16

Please help.Thanks

Re: Regarding implementation of Calendar visualization

niketn — Thu, 13 Jul 2017 07:17:25 GMT

@duraji, I have cleaned up your query based on data necessary for your final timechart (since everything else will anyways be removed after timchart command)

<Your Base Search>
| eval _time=strptime(COBDATE,"%Y%m%d")
| timechart span=1d count(GC)

As per sample data COPDATE will be YYYYMMDD format based upon which you want to plot timechart. Please try out and let me know if it works.

Re: Regarding implementation of Calendar visualization

duraij — Thu, 13 Jul 2017 17:18:11 GMT

Hi ,
Thanks for the reply ,but no this doesn't give me any results. I want to display fields "BankCTime" and "GCTime" (these are eventime fields and I have extracted only hours.minutes for these fields) in CalendarHeatMap Visualization.So it should be COBDATE(_time) against GCTime(13.12-hour.minute) and BankCTime(14.12-hour.minute).

Re: Regarding implementation of Calendar visualization

niketn — Fri, 14 Jul 2017 02:15:14 GMT

Your working query in your example had following as the final pipe

| timechart span=1d count(GCTime) by COBDATE, which implies you are doing count of GCTime by COBDATE. I had filtered query according to the same. With Calendar Heat Map you can split visuals by COBDATE or something else in the by clause.

I dont think your current data qualifies for a visualization through Calendar Heat Map. Can you mock the desired output screenshot?

Re: Regarding implementation of Calendar visualization

duraij — Mon, 17 Jul 2017 16:54:31 GMT

Hi,
My intention is to display green or Red color in the calendarHeatmap fro each date according to the range of the BankCtime and GCTime for e.g if BankCtime is between the range( 13.15 to 14.15 -Display in Green ) or (15.15to 15.30 -Orange) or (16.00 to 16.30 -Red).Please let me know how I can achieve this.
DATE BankCTime GCTime ResultBankCtime ResultGCTime
20160701 14.15 13.12 Green Orange

20160702 13.10 15.16 Red Green

Re: Regarding implementation of Calendar visualization

duraij — Wed, 19 Jul 2017 23:26:05 GMT

Hi Please let me know how I can achieve the above I am still having difficulty.

Thanks

Re: Regarding implementation of Calendar visualization

niketn — Thu, 20 Jul 2017 10:25:25 GMT

@duraij , you want to use hour.minute value as range for BankCTime and GCTime. The ranges should be continuous otherwise the gaps will pickup unwanted colors. Calendar map contains ranges starting from 0 and it adjusts color dynamically based on max value and number of buckets.

In your case you can divide your data into three ranges
Green: <15.15 --> Range=2
Yellow(light Green for Calendar Heat Map Visualization): >=15.15 AND <16--> Range=1
Red: >=16 --> Range=0

If you assign colors to Calendar Heat Map with Min Color Red and Max Color Green and define three bins then, above should format the data as per your need.

Following part of the query creates dummy data

| makeresults
| eval DATE ="20170715"
| eval BankCTime  = "14.15"
| eval GCTime  = "13.12"
| append [
  | makeresults
  | eval DATE ="20170716"
  | eval BankCTime  = "13.10"
  | eval GCTime  = "15.16"
  ]
| append [
  | makeresults
  | eval DATE ="20170717"
  | eval BankCTime  = "16.10"
  | eval GCTime  = "17.16"
  ]  
| append [
  | makeresults
  | eval DATE ="20170718"
  | eval BankCTime  = "15.20"
  | eval GCTime  = "14.16"
  ]

Following is the actual query for timechart based on your tabular data (PS: Days with No Data will be Red | fillnull value=0 😞

| eval _time=strptime(DATE,"%Y%m%d")
| timechart span=1d values(BankCTime) as BankCTime values(GCTime) as GCTime
| eval BankCTime= case(BankCTime<15.15,2,BankCTime>=15.15 AND BankCTime<16,1,BankCTime>=16,0)
| eval GCTime= case(GCTime<15.15,2,GCTime>=15.15 AND GCTime<16,1,GCTime>=16,0)
| fillnull value=0

Following is run anywhere SimpleXML

  <row>
    <panel>
      <viz type="calendar_heatmap_app.calendar_heatmap">
        <search>
          <query>| makeresults
    | eval DATE ="20170715"
    | eval BankCTime  = "14.15"
    | eval GCTime  = "13.12"
    | append [
      | makeresults
      | eval DATE ="20170716"
      | eval BankCTime  = "13.10"
      | eval GCTime  = "15.16"
      ]
    | append [
      | makeresults
      | eval DATE ="20170717"
      | eval BankCTime  = "16.10"
      | eval GCTime  = "17.16"
      ]  
    | append [
      | makeresults
      | eval DATE ="20170718"
      | eval BankCTime  = "15.20"
      | eval GCTime  = "14.16"
      ]   
    | eval _time=strptime(DATE,"%Y%m%d")
    | timechart span=1d values(BankCTime) as BankCTime values(GCTime) as GCTime
    | eval BankCTime= case(BankCTime&lt;15.15,2,BankCTime&gt;=15.15 AND BankCTime&lt;16,1,BankCTime&gt;=16,0)
    | eval GCTime= case(GCTime&lt;15.15,2,GCTime&gt;=15.15 AND GCTime&lt;16,1,GCTime&gt;=16,0)
    | fillnull value=0</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="calendar_heatmap_app.calendar_heatmap.cellPadding">2</option>
        <option name="calendar_heatmap_app.calendar_heatmap.cellSize">10</option>
        <option name="calendar_heatmap_app.calendar_heatmap.cellStyle">square</option>
        <option name="calendar_heatmap_app.calendar_heatmap.legendType">independent</option>
        <option name="calendar_heatmap_app.calendar_heatmap.maxColor">#269489</option>
        <option name="calendar_heatmap_app.calendar_heatmap.minColor">#d93f3c</option>
        <option name="calendar_heatmap_app.calendar_heatmap.numOfBins">3</option>
        <option name="calendar_heatmap_app.calendar_heatmap.showLegend">true</option>
        <option name="calendar_heatmap_app.calendar_heatmap.splitMonths">true</option>
        <option name="drilldown">none</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </viz>
    </panel>
  </row>

Re: Regarding implementation of Calendar visualization

duraij — Fri, 21 Jul 2017 19:30:00 GMT

Thanks a lot for response.But I don't get any results for the query.I drill down that the timechart command is not fetching me any results for GCTime and BankCTime (timechart span=1d values(BankCTime) as BankCTime values(GCTime) as GCTime).whereas when I use anyother command like "chart values(BankCTime) as BankCTime values(GCTime) as GCTime" or "table BankCtime GCTime" gives the result.Please let me know what I am missing.

Re: Regarding implementation of Calendar visualization

niketn — Fri, 21 Jul 2017 20:34:58 GMT

In your last example table prior to my answer, you have mentioned String Date field DATE however, in your initial clarification it was COBDATE. Please ensure you are converting correct date field from string time to epoch time.

If it is COBDATE, use the following prior to timechart command

| eval _time=strptime(COBDATE,"%Y%m%d")
PS: Timechart will work with _time field containing epoch time. So you should convert String Date field to epoch and assign the same to _time.

Also based on your clarification per day you should have only one GCTime and BankCTime for values() to work or else you might have to use max() or last() instead of values(). For example: max(GCTime) or last(GCTime).

Re: Regarding implementation of Calendar visualization

duraij — Fri, 21 Jul 2017 21:12:13 GMT

Hi ,Yes I am converting my Date(COBDATE) to EpochTime. and yes I have only one value of GCTime and BankCTime per day.

Re: Regarding implementation of Calendar visualization

niketn — Sat, 22 Jul 2017 04:55:55 GMT

If you run command | table _time GCTime BankCTime are you seeing expected results? Ideally timechart should work then.

You mentioned that chart command was working fine for you. Can you try using chart as timechart command by introducing by _time clause and also span=1d for daily bucket?

| chart span=1d values(BankCTime) as BankCTime values(GCTime) as GCTime by _time

Re: Regarding implementation of Calendar visualization

duraij — Tue, 25 Jul 2017 14:48:35 GMT

Thanks and appreciate your replies.Yes Chart ,table commands worked issue was only with timechart seems weird. But I was able to do a work around.
Thanks

Re: Regarding implementation of Calendar visualization

niketn — Tue, 25 Jul 2017 15:59:41 GMT

Can you please post the workaround for other's reference?
Were you able to try out chart with span=1d and aggregated by _time?

Re: Regarding implementation of Calendar visualization

duraij — Tue, 25 Jul 2017 16:05:28 GMT

Sure.Span cannot be used with chart command as far as I know. Also tried it, dint work.

Re: Regarding implementation of Calendar visualization

niketn — Tue, 25 Jul 2017 18:56:34 GMT

Yes span is available with chart command: https://docs.splunk.com/Documentation/Splunk/6.6.2/SearchReference/Chart#Span_options

If you have tried span with chart and it did not work, can you post the command that you tried?

Further, if chart command is working for you and chart along with span and split by _time is not working, then it will confirm that _time field is not available when you are trying to plot the chart. For which you will need a command like | eval _time=strptime(COBDATE,"%Y%m%d") before calling the chart command as I had mentioned earlier.

In any case if you have solved your issue with a work-around, you can post the same for other's reference and close this 🙂

Re: Regarding implementation of Calendar visualization

duraij — Mon, 21 Aug 2017 17:37:45 GMT

Hi,

I was able to create the calendar heatmap visuvalization but havig difficulty with the accurate display.

convert ctime(DATE),timeformat="%Y-%m-%d" |eval BankCTime=case(BankCTime>="0" AND BankCTime<="13.30","0", BankCTime>="13.30" AND BankCTime<="14.00","1", BankCTime>"14.00" AND BankCTime>="14.01","2")   |eval GCTime=case(GCTime>="0" AND GCTime<="15.0","0",GCTime>="15.01" AND GCTime<="15.30","1", GCTime<="15.30" AND GCTime>="15.31","2")|eval _time=strptime(DATE,"%Y-%m-%d")|timechart span=1d max(BankCTime) as IBTime values(GCTime) as GCTime

When I select Statistic visualization, the data is all correct, but when I select the Calendar Visualization for the same, I see that the Friday's data is listed for Thursday and Monday data is listed as Sunday's. I am not sure about this shift of data. Please help.

Re: Regarding implementation of Calendar visualization

niketn — Tue, 22 Aug 2017 06:14:09 GMT

Can you add screenshots from stats and visualization tabs? Most likely this would be happening due to User's Time Zone. On the top Splunk Bar next to Messages click on the User Name and choose Account Settings. Under the Global section select the TimeZone as per the need.

Re: Regarding implementation of Calendar visualization

duraij — Tue, 22 Aug 2017 13:47:15 GMT