Log query and usage costs

[MichalBojko]

Hi, 

I am getting familiar with OOTS Log Query and Usage Costs dashboard and when changing the timeframe to 2hours I usually get strange results in the "Top 10 queries by volume". Some of the entries are "null". What is the reason for this?

I can see that there is a query behind it that pulls billing_usage_event. 

fetch dt.system.events
| filter event.kind == "BILLING_USAGE_EVENT" and event.type == "Log Management & Analytics - Query" and event.version == "1.0"
| filter in("ALL", $User) OR in(user.email, $User)
| filter in("ALL", $Application) OR in(client.application_context, $Application)
| sort billed_bytes desc
| limit 10
| lookup [
fetch dt.system.query_executions
],
sourceField: query_id,
lookupField: query_id,
prefix: "executiondata.",
executionOrder:leftFirst
| fields
timestamp=toTimestamp(executiondata.timestamp),
query_string=executiondata.query_string,
query_volume=toDouble(billed_bytes),
query_cost = toDouble(billed_bytes)/1024/1024/1024*toDouble($Price),
User=user.email,
App=client.application_context
| fieldsRename `Query string`=query_string, `Query volume`=query_volume, `Query cost`=query_cost
| sort `Query volume` desc

I use Demo Live environment.

[Mohamed_Hamdy]

Hi @MichalBojko ,

I’ve checked the same scenario in the demo environment, and it appears that the issue is due to the query_id not matching between dt.system.events and dt.system.query_executions.

Best regards,
Mohamed

[MichalBojko · Accepted solution]

Thanks ! This help in a way, It is not my local problem.
What I heard is that two datasets used in the query are not synchronized, meaning one is "refreshed" more frequently than the other. For that reason this situation is "as expected". Way to overcome is to use 24h timeframe.