Showing data hourly

[prathamesh7]

Hi Team,

I need to display results of filter i am running in visualisation to show for each hour of a day.

When i run filter, i can see date for today but not each hour of day , it is showing with gap of 3 hours, so my question is how can i show all the hours of a day (0,1,2,..23) with their values.

How can i achieve this in best way?

Do i need to use scripted field something like below?
Using this gives error. I do not have date field.

 doc['@timestamp'].date.hourOfDay

Thanks,

[Stratoula_Kalafateli]

You can create a scripted field (or runtime field in more recent kibana versions).

So for example I have created this scripted field:

doc['timestamp'].value.hourOfDay

and I have created the following chart

[prathamesh7]

Hello @Stratoula_Kalafateli, Thanks for your reply.

Created scripted field as below.

I got below error in discover tab when i added doc['@timestamp'].date.hourOfDay scripted field. Also it only shows the scripted field and not any other fields in Discover and no data is displayed.

Below is the response from above error.

{
  "took": 79,
  "timed_out": false,
  "_shards": {
    "total": 15,
    "successful": 14,
    "skipped": 0,
    "failed": 1,
    "failures": [
      {
        "shard": 0,
        "index": "access_server-2021.07.15",
        "node": "oygWDlmkUJN6lHnw",
        "reason": {
          "type": "script_exception",
          "reason": "runtime error",
          "script_stack": [
            "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:94)",
            "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:41)",
            "doc['timestamp'].value.hourOfDay",
            "    ^---- HERE"
          ],
          "script": "doc['timestamp'].value.hourOfDay",
          "lang": "painless",
          "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "No field found for [timestamp] in mapping with types []"
          }
        }
      }
    ]
  },
  "hits": {
    "total": 72,
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "2": {
      "buckets": [
        {
          "key_as_string": "2021-07-15T10:00:00.000+05:30",
          "key": 1626323400000,
          "doc_count": 72
        }
      ]
    }
  },
  "status": 200
}

Before adding scripted field i can see data in discover,

but after adding scripted field it shows no fields to choose and no data.

Thanks,

[Stratoula_Kalafateli]

I think that it fails because your timefield is not timestamp but @timestamp. So can you please try changing your script to doc['@timestamp'].value.hourOfDay?

`

[prathamesh7]

Hello @Stratoula_Kalafateli, Thanks for your quick reply.

After changing scripted field as said it is not giving error now. I did aggregation same as you, but i cannot see auto option in Minimum interval (v 7.4) so i choose it to 1 and i am getting below.

Data is available only between 4 AM UTC so its showing in graph, however i still can't see all the hours of a day with their whatever values, that is something like below

Hrs total hits

0:  23
1:  0
2:  35
3:  15
4:  72
.
.

Thanks,

[prathamesh7]

Hi @Stratoula_Kalafateli,

I am able to get the desired results by selecting Show empty buckets and Extends bounds between 0 to 23

curl works fine in dev_tools, although sometimes gives me #! Deprecation: Use of the joda time method [getHourOfDay()] is deprecated. Use [getHour()] instead. but same curl is not working from outside kibana i.e from server.

curl from kibana shows results properly

GET /access*/_search?pretty
{
  "aggs": {
    "2": {
      "histogram": {
        "script": {
          "source": "doc['@timestamp'].value.hourOfDay",
          "lang": "painless"
        },
        "interval": 1,
        "min_doc_count": 0,
        "extended_bounds": {
          "min": 0,
          "max": 23
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {
    "custom_hour": {
      "script": {
        "source": "doc['@timestamp'].value.hourOfDay",
        "lang": "painless"
      }
    }
  },
  "docvalue_fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "bool": {
            "should": [
              {
                "match_phrase": {
                  "log.file.path": "/access-1.0/log/access_audit.log"
                }
              }
            ],
            "minimum_should_match": 1
          }
        },
        {
          "match_phrase": {
            "Request_URI": {
              "query": "\"/next2-isp/v1/*\""
            }
          }
        },
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2021-07-14T18:30:00.000Z",
              "lte": "2021-07-15T18:29:59.999Z"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

same above curl (just by adding curl -u elastic:xxxx -XGET "http://localhost:9200/access*/_search?pretty" -H 'Content-Type: application/json' -d') is giving below error,

{
  "error" : {
    "root_cause" : [
      {
        "type" : "script_exception",
        "reason" : "compile error",
        "script_stack" : [
          "doc[@timestamp].value.hourOfD ...",
          "    ^---- HERE"
        ],
        "script" : "doc[@timestamp].value.hourOfDay",
        "lang" : "painless"
      },
      {
        "type" : "script_exception",
        "reason" : "compile error",
        "script_stack" : [
          "doc[@timestamp].value.hourOfD ...",
          "    ^---- HERE"
        ],
        "script" : "doc[@timestamp].value.hourOfDay",
        "lang" : "painless"
      },
      {
        "type" : "script_exception",
        "reason" : "compile error",
        "script_stack" : [
          "doc[@timestamp].value.hourOfD ...",
          "    ^---- HERE"
        ],
        "script" : "doc[@timestamp].value.hourOfDay",
        "lang" : "painless"
      },
      {
   .
   .
   .
   .
"type" : "search_phase_execution_exception",
    "reason" : "all shards failed",
    "phase" : "query",
    "grouped" : true,
    "failed_shards" : [
      {
        "shard" : 0,
        "index" : "access_server-2021.06.30",
        "node" : "oygWDlpVJN6lHTYnw",
        "reason" : {
          "type" : "script_exception",
          "reason" : "compile error",
          "script_stack" : [
            "doc[@timestamp].value.hourOfD ...",
            "    ^---- HERE"
          ],
          "script" : "doc[@timestamp].value.hourOfDay",
          "lang" : "painless",
          "caused_by" : {
            "type" : "illegal_argument_exception",
            "reason" : "unexpected character [@].",
            "caused_by" : {
              "type" : "lexer_no_viable_alt_exception",
              "reason" : null
            }
          }
        }
      },
      {
        "shard" : 0,
        "index" : "access_server-2021.07.01",
        "node" : "oygWDlpV6lHTYnw",
        "reason" : {
          "type" : "script_exception",
          "reason" : "compile error",
          "script_stack" : [
            "doc[@timestamp].value.hourOfD ...",
            "    ^---- HERE"
          ],
          "script" : "doc[@timestamp].value.hourOfDay",
          "lang" : "painless",
          "caused_by" : {
            "type" : "illegal_argument_exception",
            "reason" : "unexpected character [@].",
            "caused_by" : {
              "type" : "lexer_no_viable_alt_exception",
              "reason" : null
            }

Thanks,

[prathamesh7]

Hi All,

Can someone please reply.

Thanks,

[prathamesh7]

able to solve above error by replacing ' with \u0027.

"source": "doc[\u0027@timestamp\u0027].value.hourOfDay",

Thanks,