cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

PRO TIP - DQL for Query Usage Cost and Audit.

Dant3
Pro

So, I  assumed that something like this might be already posted. But since I couldn't find it I will just drop it here, just in case.

So with the new and shiny DQL and GA of DPS moving forward, we need a way to "Have a glance" at what the hell the users are querying. I expect that the lovely "Consumption" gets back for DPS at some point. But while we wait let's fix it with a Query lol. 

So these two use cases have the same basic query:

 

 

 

fetch dt.system.events
| filter matchesValue(event.kind, "QUERY_EXECUTION_EVENT")
| sort timestamp DESC

 

 

 

So for those that do not know, there are a bunch of buckets with system info... cool info.

But with the event kind "QUERY_EXECUTION_EVENT" you get those juicy executed queries, doing a little math you can get (based on the default rate card) how much GB and $$$ are being dropped in those queries: 

 

 

 

fetch dt.system.events
| filter matchesValue(event.kind, "QUERY_EXECUTION_EVENT")
| fieldsAdd consumidoGiB = ((scanned_bytes) * 0.000000000931322574615478515625) 
| fieldsAdd consumidoGiBCosto = consumidoGiB * 0.00365 
| fieldsKeep timestamp, consumidoGiBCosto, consumidoGiB, scanned_bytes, analysis_timeframe.start, analysis_timeframe.end, client.application_context, query_string, user, user.email
| summarize Cost = sum(consumidoGiBCosto), by:{user.email}
| sort Cost desc

 

 

 

Look to those Juicy USD spent in queries!

Dant3_1-1708562455218.png

And from where?

Dant3_0-1708564004825.png

Anyhow.. this consumes too.. but so far I have only seen consumptions of maybe 11 MiB scanned, for 30 days in a "big" environment. So is cheap (IMO)

Now.. one could also do something similar for those buckets that ARE important... you know, the one that might have PII stuff maybe. 

 

 

 

fetch dt.system.events
| filter matchesValue(event.kind, "QUERY_EXECUTION_EVENT")
| fieldskeep timestamp, bucket, delivered_records, status, user, user.email, query_string
| sort timestamp DESC

 

 

 

It is pretty much a simple query, the result is self-explanatory.

Dant3_2-1708563357232.png

So, anyway hope you guys find it useful to track down those users who might consume... a little too much or maybe those apps and automations that have a high cost!

Cheers

Services Solution Engineer @PowerCloud - Observability/CloudOps Certified / Former SE @Dynatrace.
4 REPLIES 4

DanielS
DynaMight Guru
DynaMight Guru

Great, thanks @Dant3 

The true delight is in the finding out rather than in the knowing.

cesarsaravia
Dynatrace Mentor
Dynatrace Mentor

Amazing tip @Dant3, thanks for sharing! 

-César S. - LATAM Solutions Architect

tijust1
Advisor

Thats interesting. Thanks @Dant3 for sharing this.

Dynatrace Professional Certified

Thats interesting. Thanks

Featured Posts