Is it possible to aggregate metrics by the hour of the day?
Article ID: 380957
Updated On:
Products
Issue/Introduction
We would like to know if it is possible to know how many requests his application has during each hour of the day in a certain period, such as last month.
For example, during the month of August, there were X requests between 1pm and 2pm. Between 2pm and 3pm were Y requests, and so on.
If possible, how could I do this and how do I configure it on a dashboard?
Resolution
Development came up with an example with NasssQL. See Metric Aggregations Query Reference
It includes a section of query execution
Sample using NassQL
{
"query": [
{
"op": "FROM",
"querySpecifier": {
"op": "SPEC",
"sourceNameSpecifier": {
"op": "EXACT",
"names": [
"SuperDomain|apmservices|metadata|001"
]
},
"attributeNameSpecifier": {
"op": "REGEX",
"pattern": "Beans\\|Health Monitor\\|livenessState:.*"
}
},
"queryRange": {
"rangeSize": 2592000000
}
},
{
"op": "WINDOW_CALENDAR",
"calendarInterval": "HOUR"
},
{
"op": "SCRIPT",
"inputColumns": [
"window.timestart",
"window.timeend"
],
"outputColumns": [
"startHour",
"endHour"
],
"script": "(function nassqlfn(row) {const formatHour = (epoch) => {return String(new Date(epoch).getHours()).padStart(2, '0');};return [formatHour(row[0]), formatHour(row[1])];})"
},
{
"op": "WINDOW_CALENDAR"
},
{
"op": "GROUP",
"columns": [
"startHour",
"endHour"
]
},
{
"op": "SUM",
"column": "data.value",
"as": "TOTAL"
},
{
"op": "KEEP",
"columns": [
"startHour",
"endHour",
"TOTAL"
]
}
]
}
Small explanation of the query
- We get all required records in the given query range in the OP FROM. Here if you are placing in dashboards please remove the query range so it will pick from the global time selection on the dashboard.
- Group by one hour windows, you can also specify timezone in the operation window calendar. (See below after the dotted line) Default is UTC
- A script function that transforms the calendar hour time from above step to start hour and end hour
- Clears the hourly grouping and groups by startHour and end Hour. So this grouping would result in 24 sets for any given time range.
- Perform sum of all the data values. This will give you the below response
[["startHour","endHour","TOTAL"],
["00","01",16092],
["01","02",15815],
["02","03",15639],
["03","04",15939],
["04","05",15549],
["05","06",15465],
["06","07",14892],
["07","08",14744],
["08","09",15073],
["09","10",14959],
["10","11",14725],
["11","12",14725],
["12","13",14779],
["13","14",14727],
["14","15",14837],
["15","16",15006],
["16","17",14962],
["17","18",14852],
["18","19",13231],
["19","20",16768],
["20","21",19491],
["21","22",17021],
["22","23",16495],
["23","00",15136]]
You can use only a few visualization charts in dashboards to view this data and it is unlikely if the graph chart supports this
--------------------------
1.13. WINDOW_CALENDAR
Group rows by time specified as calendar interval in milliseconds.
The function adds window.timestart, window.timeend columns to the result and includes them in the grouping key. Subsequent aggregation functions use the grouping key.
window.timestart - The window time start for the metric value in milliseconds
window.timeend - The window time end for the metric value in milliseconds.
Using the WINDOW_CALENDAR function multiple times in a query always overrides the time aggregation set by the previous WINDOW_CALENDAR function in the upstream.
Using WINDOW_CALENDAR without parameters clears the time grouping set by a WINDOW_CALENDAR function in the query upstream.
{
"op": "WINDOW_CALENDAR",
"calendarInterval": [ "MINUTE" | "HOUR" | "DAY" | "WEEK" | "MONTH" | "QUARTER" | "YEAR" ],
"timeZone": string
}
calendarInterval - Used to configure calendar-aware intervals. You can specify calendar intervals using the below values,
MINUTE - All minutes begin at 00 seconds. One minute is the interval between 00 seconds of the first minute and 00 seconds of the following minute in the specified time zone.
HOUR - All hours begin at 00 minutes and 00 seconds. One hour (1h) is the interval between 00:00 minutes of the first hour and 00:00 minutes of the following hour in the specified time zone.
DAY - All days begin at the earliest possible time, which is usually 00:00:00 (midnight). One day (1d) is the interval between the start of the day and the start of the following day in the specified time zone.
WEEK - One week is the interval between the start day_of_week:hour:minute:second ( Monday ) and the same day of the week and time of the following week in the specified time zone.
MONTH - One month is the interval between the start day of the month and time of day and the same day of the month and time of the following month in the specified time zone.
QUARTER - One quarter is the interval between the start day of the month and time of day and the same day of the month and time of day three months later.
YEAR - One year is the interval between the start day of the month and time of day and the same day of the month and time of day the following year in the specified time zone.
timeZone - (optional, default: UTC) By default, all bucketing and rounding is done in UTC. Use the timeZone parameter to indicate that bucketing should use a different time zone. You can specify time zones as an ISO 8601 UTC offset (e.g. +01:00 or -08:00) or as an IANA time zone ID ( https://nodatime.org/TimeZones ), such as America/Los_Angeles. Please note we support limited set of three-letter time zone IDs (https://docs.oracle.com/javase/8/docs/api/java/time/ZoneId.html#SHORT_IDS). Using the three-letter time zone Ids never take Daylight savings time into account. It is strongly recommended to use the IANA TZDB.
Example 12. Group metric values by MONTH.
// Request
{
"query": [
{
"op": "FROM",
"querySpecifier": {
"op": "SPEC",
"sourceNameSpecifier": {
"op": "EXACT",
"names": [
"SuperDomain|apmservices|metrics|001"
]
},
"attributeNameSpecifier": {
"op": "EXACT",
"names": [
"REST:Responses Per Interval"
]
}
},
"queryRange": {
"rangeSize": 120000
}
},
{
"op": "WINDOW_CALENDAR",
"timeZone":"Antarctica/Macquarie",
"calendarInterval": "MONTH"
}
]
}
Output from the original sample
// Response
[["window.timestart","window.timeend","data.time","data.value","metric.source","metric.path"],
[1654005600000,1656597600000,1655548200000,0,"SuperDomain|apmservices|metrics|001","REST:Responses Per Interval"],
[1656597600000,1659276000000,1658313000000,1,"SuperDomain|apmservices|metrics|001","REST:Responses Per Interval"],
[1659276000000,1661954400000,1661077800000,0,"SuperDomain|apmservices|metrics|001","REST:Responses Per Interval"],
[1698757200000,1701349200000,1700389800000,1,"SuperDomain|apmservices|metrics|001","REST:Responses Per Interval"]]
Additional Information
Feedback
