Archive

Regarding implementation of Calendar visualization

New Member

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.

Tags (1)
0 Karma

Explorer

alt text

0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

@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 datehour or not.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

Hi,Sorry for the late response.I was not able to run the above command since datemday datewday 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.

0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

@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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"