Azure Log Analytics: looking at data and costs – Part 3

Part1: http://approjects.co.za/?big=en-gb/industry/blog/cross-industry/2019/03/28/azure-log-analytics-looking-at-data-and-costs/
Part2: http://approjects.co.za/?big=en-gb/industry/blog/cross-industry/2019/05/09/azure-log-analytics-looking-at-data-and-costs-part-2/
Part3 – This post : http://approjects.co.za/?big=en-gb/industry/blog/cross-industry/2019/07/18/azure-log-analyt…and-costs-part-3/

There are two parts to this post:

1. Predict Forward
2. Add more computers

1. Predict forward

In the previous two posts on this topic, we’ve seen the data ‘as is’ and in the past (normally the past month) – but how to we predict the usage from that?
In the following example, you can use 30days of historical data and predict forward 30days to see the Data Capacity


//
// Predict data volume for the next month
//
let startDate = startofday(ago(30d)); // go back in time nn days
let endDate = now(); // what is the date now
let projectTo = now()+30d; // project forward nn days
let projectForward = 30; // must be same as projectTo value
union withsource = tt *
| where TimeGenerated between (startDate .. endDate )
| where _IsBillable == True
| make-series BillingVolumeNow = avg(_BilledSize) default=0 on TimeGenerated in range(startDate, projectTo, 1h)
| extend BillingForecast = series_decompose_forecast(BillingVolumeNow, projectForward*24)
| render timechart title = "Predicted Data Capacity in 30days "


I think its more interesting to work in GB than Bytes (as per the above example); also Azure Billing and Azure Cost Calculator use GB, so use this amended query


//
// Predict data volume for the next month
//
let startDate = startofday(ago(30d)); // go back in time nn days
let endDate = now(); // what is the date now
let projectTo = now()+30d; // project forward nn days
let projectForward = 30; // must be same as projectTo value
union withsource = tt *
| where TimeGenerated between (startDate .. endDate )
| where _IsBillable == True
| make-series BillingVolumeNow = avg(_BilledSize / (1024*1024*1024)), default=0 on TimeGenerated in range(startDate, projectTo, 1h)
| extend BillingForecast = series_decompose_forecast(BillingVolumeNow, projectForward*24)
| render timechart title = "Predicted Data Capacity (GB) in 30days "


You can run this in our demo workspace here

Output:

Graph

2. Add More Computers

This query, calculates the effect of adding some new Computers. The query starts like the others you’ve seen from me. However in the “summarize” section. We look at the count of computers found, how much on average data they have sent (in past 31days – I did this in MBytes). I then calculate adding more computers using the average log per month . In the example I add an extra 10 computers and this will give us the approx change to the data (in GB).

let addExtraComputers=10;
let daystoSearch = 31d;
union withsource = tt *
| where TimeGenerated > ago(daystoSearch)
| where _IsBillable == true
| where Computer != ""
| summarize
ComputerCount = dcount(Computer),
MbperComputer = round(sum(_BilledSize / (1024*1024)) / dcount(Computer),4),
TotalGBytes = round(sum(_BilledSize / (1024*1024*1024)),4),
PredictedGB = round(sum(_BilledSize / (1024*1024*1024)) / dcount(Computer) * (dcount(Computer) + addExtraComputers),4)


Run it here

Output:

Also, you can filter on just the Windows Event Log to see the change per solution:

let addExtraComputers=10;
let daystoSearch = 31d;
union withsource = tt *
| where TimeGenerated > ago(daystoSearch)
| where _IsBillable == true
| where Computer != ""
| where tt == "Event"
| summarize
ComputerCount = dcount(Computer),
MbperComputer = round(sum(_BilledSize / (1024*1024)) / dcount(Computer),4),
TotalGBytes = round(sum(_BilledSize / (1024*1024*1024)),4),
PredictedGB = round(sum(_BilledSize / (1024*1024*1024)) / dcount(Computer) * (dcount(Computer) + addExtraComputers),4)