Following up on the work I did in an earlier post, I wanted to create a chart of the Log Analytics table sizes over the course of a week. A quick Google (kql show table sizes over time) got me to these two posts 1 & 2 and what they were doing seemed quite complicated and not fit for a daily snapshot kind of report I was looking for.
That’s when my colleague mentioned the Usage table. That has hourly data for all the tables in Log Analytics. So it was as easy as doing the following:
|
1 2 3 4 5 |
Usage | where TimeGenerated > ago(30d) | where DataType in ("SigninLogs","AADServicePrincipalSignInLogs","AADNonInteractiveUserSignInLogs","AADManagedIdentitySignInLogs") | summarize QuantityGB = sum(Quantity)/1024 by bin(TimeGenerated, 1d) | render columnchart |
This gives me a nice chart like this.
If I want to tease it out by tables too, I could do:
|
1 2 3 4 5 |
Usage | where TimeGenerated > ago(30d) | where DataType in ("SigninLogs","AADServicePrincipalSignInLogs","AADNonInteractiveUserSignInLogs","AADManagedIdentitySignInLogs") //("AADNonInteractiveUserSignInLogs") // ("SigninLogs","AADServicePrincipalSignInLogs","AADNonInteractiveUserSignInLogs") | summarize QuantityGB = sum(Quantity)/1024 by DataType, bin(TimeGenerated, 1d) | render columnchart kind=stacked |
To get:
Remove the kind=stacked to get separate columns per table.