KQL Search

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_IsBillableComputer_ResourceIdisnotemptybilledDatafreeDataTotal_Datadescformat_bytesbilledData1freeData1total_d

Operators

findwhereprojectisnotemptysummarizesumifbyextendorder byformat_bytesproject-away

Actions

GitHub