Regarding implementation of Calendar visualization

[accgarima]

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.

[duraij]

[duraij]

I was not able to upload this under comments. I have 3 bins and have assigned min color as "greenish-yellow" and max color as "Green". Please check.

[niketn]

From settings you can change the color bins. Min color as Red and Max color as Green. It should pick middle color as orange.

[duraij]

Yes.I tried that it gives me three colors no issues in that.But the problem is that the dates are shifted(Friday is displayed with Saturday's data and sunday with Monday's Data).

[niketn]

Can you run just your base search to pull one event? Validate whether the times on the _raw Event matches with _time timestamp for the same event in the Event Viewer? This is to confirm whether your data is in the same timezone as logged in user's timezone or not.

This could also be possible because you are overriding _time field with the field that you have calculated. Would it be possible for you to paste couple of events(mock up or anonymize and sensitive data). If you have Splunk Entitlement, you can also reach out to Splunk Support for them to assist you with your scenario.

[duraij]

Hi, even though I was able to see values for Friday and Monday after changing the timezone to EST but those values were incorrect since my data is in GMT timezone and my user profile is also in Gmt.still having the same issue of Friday data displayed on saturday and Monday data displayed on Tuesday.

[niketn]

@duraij, this seems to be issue with timestamp identification. Please have your Splunk admin look at the issue. You can confirm the same by adding date_hour and date_mday to your base search and validate the event timestamp. For example check if date_hour=23 date_mday=8 date_mday="wednesday" date_month="september", pulls data from wednesday or thursday. Verify _time is in sync with raw data timestamp and date_hour or not.

[duraij]

I am not able to accept anything as Answer .I don't see an option. Reg the data, I see that the values and Dates are right when represented as a table but when I see select the calendarHeatmap Visualization its incorrect.

[niketn]

Have you verified through the following?

 <Your Base Search>
| table _time date_mday date_wday date_hour

If the hour value of date_hour corresponds to hour value of _time, then you should report this issue as bug for Calendar heatmap visualization. I will try to test this out and revert back. Do let me know your Splunk Enterprise Version and Calendar Heatmap version.

[duraij]

Hi,Sorry for the late response.I was not able to run the above command since date_mday date_wday date_hour doesn't give me any results. but as I mentioned earlier "statistical view"/Table provides me the right data but only when I select Calendar Heatmap the data is overlapped and Mondays data is displayed as Sunday's.

[duraij]

HI, You were right the issue was because of the timezone but I still dont see data for some dates but I will figure that out.Thanks a lot. Can you convert your comment to answer so that I can accept it.Thanks.

[niketn]

@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>

[duraij]

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.

[niketn]

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).

[duraij]

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

[niketn]

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

[duraij]

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

[niketn]

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

[duraij]

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

[niketn]

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 🙂