Forum Discussion

T150732D's avatar
T150732D
Copper Contributor
Jun 27, 2022

Sentinel Billable data

Hello can you please help me understand difference of two queries we received from vendor deployin sentinel.    We have logic app running daily this query to see billable data (to monitor if we are...
cost management
data collection
mikhailf's avatar
mikhailf
Steel Contributor
Jun 28, 2022

Hello T150732D ,

 

Please, try the following queries:

 

Usage
| where TimeGenerated >= startofday(ago(1d)) and EndTime < startofday(now())
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(TimeGenerated, 1d), Solution
| summarize TotalDataGB = sum(BillableDataGB)

 

Usage
| where TimeGenerated >= startofday(ago(30d)) and EndTime < startofday(now())
| where IsBillable == true
| summarize BillableDataGB=sum(Quantity) / 1000. by bin(TimeGenerated, 1d), Solution
| render columnchart

 

It will return similar results.

T150732D's avatar
T150732D
Copper Contributor
Jul 07, 2022

mikhailf can you please help me with some query to show monthly sentinel ingestion log cost?

 

I would like to provide management total Gb but also cost in dollars.

 

thank you

 

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Jul 07, 2022

    T150732D 

     

    Example for previous 3 months (excludes current month) but you can tweak that, alter line 4  from "-1" to "-0"

    let price_ = 4.0;  // enter you price per GB for LogAnalytics + Sentinel 
Usage
// get logs for previous 3 months
| where TimeGenerated between(startofmonth(now(),-3) ..endofmonth(endofmonth(now(),-1)) )
| summarize GBytesTotal=sum(Quantity) / 1000, Price=sum(Quantity) / 1000 * price_ by month=bin(datepart("Month", TimeGenerated), 1)
// format month so we can sort it
| extend month = case(month==1,strcat(month,'. Jan'),month==2,strcat(month,'. Feb'),month==3, strcat(month,'. Mar'),month==4,strcat(month,'. Apr'),month==5,strcat(month,'. May'),month==6,strcat(month,'. Jun'),month==7, strcat(month,'. Jul'),month==8,strcat(month,'. Aug'),month==9,strcat(month,'. Sep'),month==10,strcat(month,'. Oct'),month==11,strcat(month,'. Nov'),month==12,strcat(month,'. Dec'),"error")
| order by month asc

     

     

    You'll see this in the next version of the "Workspace Usage Report" workbook, in a week or so I hope.