PRO TIP - DQL for Query Usage Cost and Audit.

[Dant3]

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!

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. 

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.

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

[DanielS]

Great, thanks @Dant3 

[cesarsaravia · Accepted solution]

Amazing tip @Dant3, thanks for sharing! 

[tijust1]

Thats interesting. Thanks @Dant3 for sharing this.

[Marcelo_santand]

Thats interesting. Thanks