03 Feb 2025 05:56 AM - edited 03 Feb 2025 06:13 AM
Heya,
Just a quick tip on Dashboards. With the addition of new Dashboards, we have the power of DQL at our hand and can use them in our Dashboards but often we end up spending a lot on Query. Sometimes we even have to compromise on some key metrics because we can't really use every possible DQL.
There are two limitations of having too many DQL tiles in a dashboard
1. We end up spending a lot on Querying.
2. Too many DQL tiles take too much time to load as each DQL has to get the result hence increasing the time it takes for overall dashboard visibility.
What if we can overcome this?
What if we found an alternative for the same.?
Let me break it down with a small example.
Let's say my Dashboard has 5 queries. (I'm just taking some random queries as example just to make this simpler)
Query1:
fetch logs
| filter matchesPhrase(content, "test")
| summarize count()
Query2:
fetch logs
| filter matchesPhrase(content, "test")
| summarize count(), by: dt.host_group.id
Query3:
fetch logs
| filter matchesPhrase(content, "test")
| summarize count(), by: {dt.system.bucket}
Query4:
fetch spans
| summarize count()
Query5:
fetch spans
| summarize count(), by: {aws.region}
Now if you really see here there are only 2 actual queries the rest are just different variations of the same.
Query2, Query3 are the same as Query1 just a different summarization.
Query5 is same as Query4 just summarized version.
Out of this we can simply have 2 queries which are Query1 and Query4 store that data in variables and the use the same in the dashboard tiles.
This way we need not even run the Query2, Query3, Query5.
Let me show it works now.
Take a variable in the Dashboard,
Select code from the dropdown.
Now in the code we use queryExecutionClient to run the query for us.
The code for Query1 would look like this.
import { queryExecutionClient } from "@dynatrace-sdk/client-query";
import { useDqlQuery } from '@dynatrace-sdk/react-hooks';
// Function to convert the timeframe to a relative format
function convertToRelativeTimeframe(timeString) {
const now = new Date();
const givenTime = new Date(timeString);
const diffInMs = now - givenTime;
const diffInSeconds = Math.floor(diffInMs / 1000);
const diffInMinutes = Math.floor(diffInSeconds / 60);
const diffInHours = Math.floor(diffInMinutes / 60);
const diffInDays = Math.floor(diffInHours / 24);
if (diffInDays > 0) {
return `now()-${diffInDays}d`;
} else if (diffInHours > 0) {
return `now()-${diffInHours}h`;
} else if (diffInMinutes > 0) {
return `now()-${diffInMinutes}m`;
} else {
return `now()-${diffInSeconds}s`;
}
}
export default async function () {
let timeFrameFrom = $dt_timeframe_from;
console.log(typeof(timeFrameFrom));
console.log(timeFrameFrom);
// Convert the timeframe to a relative format
const relativeTimeFrameFrom = convertToRelativeTimeframe(timeFrameFrom);
const queryString = `fetch logs, from: ${relativeTimeFrameFrom}
| filter matchesPhrase(content, "test")
| fields dt.host_group.id, dt.system.bucket `;
const data = await queryExecutionClient.queryExecute({
body: {
query: queryString,
maxResultRecords: 100000
}
});
// Extract and return only the requestToken
if (data && data.requestToken) {
let response;
do {
response = await queryExecutionClient.queryPoll({
requestToken: data.requestToken,
});
} while (response.state === "RUNNING");
console.log(response);
// Set-1: Count (length of the response.result.records)
const countRecords = response.result.records.length;
// Set-2: Count by dt.host_group.id
const countByHostGroupId = response.result.records.reduce((acc, record) => {
acc[record['dt.host_group.id']] = (acc[record['dt.host_group.id']] || 0) + 1;
return acc;
}, {});
// Set-3: Count by dt.system.bucket
const countBySystemBucket = response.result.records.reduce((acc, record) => {
acc[record['dt.system.bucket']] = (acc[record['dt.system.bucket']] || 0) + 1;
return acc;
}, {});
// Combine the three sets of data into an array
const combinedData = [countRecords, countByHostGroupId, countBySystemBucket];
return JSON.stringify(combinedData);
} else {
throw new Error("requestToken is null or undefined");
}
}
Here what we did is simple we Identified the common part among the queries 1, 2, 3 and formed a query that would have just enough data to replace the tiles of the respective queries.
(Also keep this variable hidden by de-selecting "Display as filter on Dashboard")
Now for the second query (Query4), the variable code would be this:
import { queryExecutionClient } from "@dynatrace-sdk/client-query";
import { useDqlQuery } from '@dynatrace-sdk/react-hooks';
export default async function () {
const queryString = `fetch spans
| fields aws.region`;
const data = await queryExecutionClient.queryExecute({
body: {
query: queryString
}
});
// Extract and return only the requestToken
if (data && data.requestToken) {
let response;
do {
response = await queryExecutionClient.queryPoll({
requestToken: data.requestToken,
});
} while (response.state === "RUNNING");
console.log(response);
return JSON.stringify(response.result.records);
} else {
throw new Error("requestToken is null or undefined");
}
}
Note: We have to convert the JSON to string to be able to get the whole data instead of breaking it. When we return it as JSON itself, sometimes when I was using there was some data that was cut out. That is the reason we are using JSON.stringify here
Also, there are two ways of doing it.
1. Like the Query1 variable code you can format all the output you need in the code itself.
2. Like the Query4 variable code you can get the data first and format it later for the tiles.
Now comes the actual part of putting the data into the original dashboard.
Now in the first tile, you replace the DQL with the following code.
Tile1:
/*
* This function will run in the DYNATRACE JavaScript runtime.
* For information visit https://dt-url.net/functions-help
*/
export default async function () {
try {
const data = JSON.parse($query1);
// Check if data is not null and is an array
if (data && Array.isArray(data)) {
// Extract the count from the result (Set-1)
const count = data[0];
console.log(count);
return count;
} else {
throw new Error("Invalid data format");
}
} catch (error) {
// Handle the error
console.error("Error:", error.message);
return "Error processing data";
}
}
Tile2:
/*
* This function will run in the DYNATRACE JavaScript runtime.
* For information visit https://dt-url.net/functions-help
*/
export default async function () {
try {
const data = JSON.parse($query1);
// Check if data is not null and is an array
if (data && Array.isArray(data)) {
// Extract the count by dt.host_group.id from the result (Set-2)
const countByHostGroupId = data[1];
// Format the data to have only two columns: dt.host_group.id and count
const formattedData = Object.entries(countByHostGroupId).map(([id, count]) => ({
'dt.host_group.id': id,
count: count
}));
console.log(formattedData);
return formattedData;
} else {
throw new Error("Invalid data format");
}
} catch (error) {
// Handle the error
console.error("Error:", error.message);
return "Error processing data";
}
}
Tile3:
/*
* This function will run in the DYNATRACE JavaScript runtime.
* For information visit https://dt-url.net/functions-help
*/
export default async function () {
try {
const data = JSON.parse($query1);
// Check if data is not null and is an array
if (data && Array.isArray(data)) {
// Extract the count by dt.system.bucket from the result (Set-3)
const countBySystemBucket = data[2];
// Format the data to have only two columns: dt.system.bucket and count
const formattedData = Object.entries(countBySystemBucket).map(([bucket, count]) => ({
'dt.system.bucket': bucket,
count: count
}));
console.log(formattedData);
return formattedData;
} else {
throw new Error("Invalid data format");
}
} catch (error) {
// Handle the error
console.error("Error:", error.message);
return "Error processing data";
}
}
This is the data that is formatted in the variable itself.
Now for the data that is not formatted earlier,
tile4 (Query4)
/*
* This function will run in the DYNATRACE JavaScript runtime.
* For information visit https://dt-url.net/functions-help
*/
export default async function () {
const data = JSON.parse($Query4);
// Get the count of elements in the data
const count = data.length;
// Return the count
return count;
}
Tile5 (Query5)
/*
* This function will run in the DYNATRACE JavaScript runtime.
* For information visit https://dt-url.net/functions-help
*/
export default async function () {
const data = JSON.parse($Query4);
// Check if data is not null and is an array
if (data && Array.isArray(data)) {
// Summarize the count based on aws.region
const summary = data.reduce((acc, record) => {
const region = record["aws.region"];
if (acc[region]) {
acc[region]++;
} else {
acc[region] = 1;
}
return acc;
}, {});
// Format the summary into the desired structure
const formattedSummary = Object.entries(summary).map(([region, count]) => ({
"aws.region": region,
"count()": count
}));
console.log(formattedSummary);
return formattedSummary;
} else {
throw new Error("Invalid data format");
}
}
Now, save and run the dashboard your dashboard will run much faster, and the dashboard querying cost is much cheaper.
Note: If you are seeing discrepancies in your data that is because of the queryLimits. Please make sure you set proper query limits when you run the query with queryExecutionClient.
Hope this helps,
Happy Querying 😉.
03 Feb 2025 06:13 AM
Infact this is very much useful to make the querying the efficiency and achieve it much straightforward way.