Hi everyone,
I’m trying to recreate the problem chart from the Problems menu using DQL, but I’m struggling to match the behavior of the native visualization.
Current approach:
fetch dt.davis.problems
| makeTimeseries count(), by: {event.status}Current result:
Expectation:
I want the problem to behave like the native Problems chart:
- The bar should start appearing from event.start
- The bar should continue to reappear based on the selected interval
- Example:
- If interval = 5 minutes
- And the problem event.status is still OPEN (or event.end is null)
- Then the same problem should continue contributing to every 5-minute bucket until it is CLOSED
So essentially I’m trying to calculate “active/open problems over time”, not just “problem creation count”.
Has anyone successfully recreated this behavior in DQL?
Is there a recommended approach/pattern for expanding problem duration across time buckets?
Thanks!
Hello @zaidanrizq!
I'm not sure if got right, but I think you want something like this:
If it's, you can use this Json below and then use as a dashboard, I made comments on the dashboard so you can understand better:
{
"tiles": {
"0": {
"content": "# Problems over time\nThis dashboard recreates the Problems chart behavior by counting problems across their active duration, from `event.start` until `event.end` or `now()`.",
"type": "markdown"
},
"1": {
"davis": {
"davisVisualization": {
"isAvailable": true
},
"enabled": false
},
"description": "Counts each problem in every interval where it was active.",
"query": "fetch dt.davis.problems, from: toTimestamp($dt_timeframe_from) - 2h, to: toTimestamp($dt_timeframe_to)\n| filter event.start <= toTimestamp($dt_timeframe_to)\n| filter coalesce(event.end, now()) >= toTimestamp($dt_timeframe_from)\n| makeTimeseries problems = countDistinct(display_id),\n by: { event.status },\n interval: 5m,\n spread: timeframe(from: event.start, to: coalesce(event.end, now()))",
"querySettings": {
"defaultSamplingRatio": 10,
"defaultScanLimitGbytes": 500,
"enableSampling": false,
"maxResultMegaBytes": 10,
"maxResultRecords": 1000
},
"title": "Problems over time by status",
"type": "data",
"visualization": "barChart",
"visualizationSettings": {
"autoSelectVisualization": false,
"chartSettings": {
"legend": {
"position": "right"
}
},
"coloring": {
"colorRules": [
{
"colorMode": "custom-color",
"comparator": "=",
"customColor": "#C91313",
"field": "event.status",
"type": "string",
"value": "ACTIVE"
}
]
}
}
},
"2": {
"davis": {
"davisVisualization": {
"isAvailable": true
},
"enabled": false
},
"description": "Same logic without splitting by status.",
"query": "fetch dt.davis.problems, from: toTimestamp($dt_timeframe_from) - 2h, to: toTimestamp($dt_timeframe_to)\n| filter event.start <= toTimestamp($dt_timeframe_to)\n| filter coalesce(event.end, now()) >= toTimestamp($dt_timeframe_from)\n| makeTimeseries problems = countDistinct(display_id),\n interval: 5m,\n spread: timeframe(from: event.start, to: coalesce(event.end, now()))",
"querySettings": {
"defaultSamplingRatio": 10,
"defaultScanLimitGbytes": 500,
"enableSampling": false,
"maxResultMegaBytes": 10,
"maxResultRecords": 1000
},
"title": "Total problems over time",
"type": "data",
"visualization": "barChart",
"visualizationSettings": {
"autoSelectVisualization": false,
"chartSettings": {
"legend": {
"position": "right"
}
}
}
}
},
"variables": [],
"annotations": [],
"settings": {
"defaultTimeframe": {
"enabled": true,
"value": {
"from": "now()-2h",
"to": "now()"
}
}
},
"layouts": {
"0": {
"h": 3,
"w": 24,
"x": 0,
"y": 0
},
"1": {
"h": 8,
"w": 24,
"x": 0,
"y": 3
},
"2": {
"h": 8,
"w": 24,
"x": 0,
"y": 11
}
},
"importedWithCode": false,
"version": 21
}I hope it helps you 😊
Featured Posts
