Query Details
Data Per Computer
Query
//Data by agented computer, split by billable, non-billable, and total
find where TimeGenerated > ago(1d) project _BilledSize, _IsBillable, Computer, _ResourceId
| where _isBillable=true and isnotempty(Computer)
| summarize billedData = sumif(_BilledSize, _IsBillable=~true),
freeData = sumif(_BilledSize, _IsBillable=~false) by Computer
| extend Total_Data = billedData + freeData
| order by billedData desc
//Same data, converted to MB
find where TimeGenerated > ago(1d) project _BilledSize, _IsBillable, Computer, _ResourceId
| where _isBillable=true and isnotempty(Computer)
| summarize billedData = format_bytes(sumif(_BilledSize, _IsBillable=~true)),
freeData = format_bytes(sumif(_BilledSize, _IsBillable=~false)),
billedData1 = sumif(_BilledSize, _IsBillable=~true),
freeData1 = sumif(_BilledSize, _IsBillable=~false)
by Computer
| extend total_d = billedData1 + freeData1
| extend Total_Data = format_bytes(total_d)
| project-away billedData1, freeData1, total_d
| order by billedData desc
Explanation
The query retrieves data from agented computers and splits it into billable, non-billable, and total. It then summarizes the data by computer and calculates the total data by adding the billable and non-billable data. Finally, it orders the results by the amount of billed data in descending order.
In the second part of the query, the data is converted to megabytes and formatted accordingly. The total data is also formatted and the unnecessary columns are removed before ordering the results.
Details
Rod Trent
Released: March 15, 2023
Tables
The queries use the "Tablename" table.
Keywords
TimeGenerated,_BilledSize,_IsBillable,Computer,_ResourceId,isnotempty,billedData,freeData,Total_Data,desc,format_bytes,billedData1,freeData1,total_d
Operators
findwhereprojectisnotemptysummarizesumifbyextendorder byformat_bytesproject-away