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

PRO TIP - DQL for Query Usage Cost and Audit.


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:




| 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: 




| 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,
| summarize Cost = sum(consumidoGiBCosto), by:{}
| sort Cost desc




Look to those Juicy USD spent in queries!


And from where?


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. 




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




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


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!


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

DynaMight Guru
DynaMight Guru

Great, thanks @Dant3 

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

Dynatrace Pro
Dynatrace Pro

Amazing tip @Dant3, thanks for sharing! 

-César S. - LATAM Solutions Architect


Thats interesting. Thanks @Dant3 for sharing this.

Dynatrace Professional Certified

Thats interesting. Thanks

Featured Posts