Azure Log Analytics: Disk Space Usage ; Part 3
Category Log Analytics Syntax v2 ; OMS Syntax
As promised here is Part 3
Part 1: https://blogs.msdn.microsoft.com/ukhybridcloud/2017/12/08/azureloganalyticsdiskspaceusage/
Part 3: this post
In this post we'll look at Trends and doing an estimate. My query now looks like the one further down this article (see Final Code Sample), let's talk about the changes:
 I'm just looking at a single Computer (this is a good idea as it's easier to graph one machine).
So I've added a  where Computer startswith
"W"
(as my computer starts with a "w" – please adjust to suit) 
Then we use the MakeSeries operator (and I suggest you look at the Charting tutorials in the docs and this Tutorial). I store the details in a variable called Y (for the YAxis)
If I just ran the code up until this line, you can see that two arrays are created, Y and TimeGenerated. 
We then need to use Extend, however the best examples for that are under Linear Regression using series_fit_line. There are a lot of "Series_nnnnn_yyyyyy" examples in the docs, such as: https://docs.loganalytics.io/docs/LanguageReference/Scalarfunctions/series_fill_forward()
The final code sample:
// Chart C: over the past nn days  Show a Trend Line
let startDate = ago(60d); // enter how many days to look back on
Perf
 where TimeGenerated > startDate
 where ObjectName == "LogicalDisk"
and CounterName == "% Free Space"
 where Computer startswith
"W"
 where InstanceName contains
"C:"
 makeseries y = min(CounterValue) on TimeGenerated in range(startDate,now(), 1d)
 extend (RSquare,Slope,Variance,RVariance,Interception,LineFit)=series_fit_line(y)
 render timechart
Which looks like this:
Note: You do need a good amount of nonzero data, to get a good trend slope. I have a few servers that are switched off a lot and that will often mean the LineFit is very wrong. I need to look at a way of seeing if there are too many data points with a value of zero– a task for another day and article
Now what if we wish to predict how many days are left before we get to zero % free.
// Chart C: over the past nn days  How many days have we got left for a Named Computer?
let startDate = ago(60d); // enter how many days to look back on
Perf
 where TimeGenerated > startDate
 where ObjectName == "LogicalDisk"
and CounterName == "% Free Space"
 where Computer startswith
"W"
// name your computer
 where InstanceName contains
"C:"
 makeseries y = min(CounterValue) on TimeGenerated in range(startDate,now(), 1d)
 extend (RSquare,Slope,Variance,RVariance,Interception,LineFit)=series_fit_line(y)
 extend DaysTo0= (1 * Interception) / Slope
 project round(DaysTo0,0)
In the above, I have made two additions:

 extend DaysTo0= (1 * Interception) / Slope
This syntax is used to calculate the days until we reach zero.

 project round(DaysTo0,0)
With the last line we just project the result, I also used Round to get rid of the extra decimal places)
This latest result gives us an output of:
Now what if we wish to predict how many days are left before we get to zero % free. Let's do this on ALL Computers
// Chart C: over the past nn days  How many days have we got left on all Computers?
let startDate = ago(60d); // enter how many days to look back on
let daysToEstimate = 365; // show computers with less than a year of disk space
Perf
 where TimeGenerated > startDate
 where ObjectName == "LogicalDisk"
and CounterName == "% Free Space"
 where InstanceName contains
"C:"
 makeseries y = min(CounterValue) on TimeGenerated in range(startDate,now(), 1d) by Computer
 extend (RSquare,Slope,Variance,RVariance,Interception,LineFit)=series_fit_line(y)
 extend DaysTo0= (1 * Interception) / Slope
 where DaysTo0 > 0
and DaysTo0 < daysToEstimate // filter any negative trending computers
 project Computer, round(DaysTo0,0)
In the above I added a daysToEstimate variable set to 1 year. I then changed the makeseries line by appending "by Computer" and removed the line to look for a single computer.
I also didn't want to see any negative values (computers that have had spaced cleared and are now trending the wrong way.
It makes a good BarChart as well
Remember with any trend calculation its only a good as the data you have, so the more data points the better. There are others ways of doing this, but this I hope is a good starter post?
That's all for now!
*** added example from the comments ***
// Chart C: over the past nn days – How many days have we got left on all Computers for all Drive Letters?
let startDate = ago(30d); // enter how many days to look back on
let daysToEstimate = 365; // show computers with less than a year of disk space
Perf
 where TimeGenerated > startDate
 where ObjectName == “LogicalDisk” and CounterName == “% Free Space”
 where InstanceName has “:” and strlen(InstanceName) == 2 // ignore non drive letters
 makeseries y = min(CounterValue) on TimeGenerated in range(startDate,now(), 1d) by Computer, InstanceName
 extend (RSquare,Slope,Variance,RVariance,Interception,LineFit)=series_fit_line(y)
 extend DaysTo0= (1 * Interception) / Slope
 where DaysTo0 > 0 and DaysTo0 < daysToEstimate // filter any negative trending computers
 project Computer , InstanceName , round(DaysTo0,0)
Hi CliveW.
1st of all, thanks for the article, it’s quite clear and understandable for someone like me ( who is starting to work with Log Analytics).
One question about your last query, is there any way of get same kind of “DaysTo0” but by distinct InstanceName? I think summarize will not solve it. Or we need to use “let” by instance name?
Thank you for your time.
Hi, Perhaps I’m misunderstanding your question but ‘daystogo’ is using a distinct instancename, as I just look for the C: drive. You could pass that value using a let to make it more readable, is that what you are asking?
e.g.
 where InstanceName contains “C:”
Or do you want to see all instancenames per computer – c:, d:, e: etc…
Hi Clive, thanks for the fast answer.
Yes i would like something to be by column= Computer, Daysto0 from C, Daystoo from D, and so on.
I think its not even possible without aggregation.
If the drives dont have to be in their own column would this work for you?
// Chart C: over the past nn days – How many days have we got left on all Computers for all Drive Letters?
let startDate = ago(30d); // enter how many days to look back on
let daysToEstimate = 365; // show computers with less than a year of disk space
Perf
 where TimeGenerated > startDate
 where ObjectName == “LogicalDisk” and CounterName == “% Free Space”
 where InstanceName has “:” and strlen(InstanceName) == 2 // ignore non drive letters
 makeseries y = min(CounterValue) on TimeGenerated in range(startDate,now(), 1d) by Computer, InstanceName
 extend (RSquare,Slope,Variance,RVariance,Interception,LineFit)=series_fit_line(y)
 extend DaysTo0= (1 * Interception) / Slope
 where DaysTo0 > 0 and DaysTo0 < daysToEstimate // filter any negative trending computers
 project Computer , InstanceName , round(DaysTo0,0)