SCOM Reporting Forecasting/trending powershell report solution
The “issue”:
The previous monitoring tool sets reporting tool had the ability to generate forecasting reports but unfortunately SCOM does not have the capability out of the box to produce these reports or perform this analysis.
I investigated other solutions and did find a couple (one was at a significant cost while another only provided graphics and not statistics/report). These didn’t seem to fit the bill so I then decided to develop my own.
So i created a Powershell script that gets the data, analyzes it and produces HTML reports. The breakdown you can see below is on how my script is structured and if you would like to create the code for yourself (best way to learn).
- get list of unix servers from resource pool specified (we use different resource pools for different gateways but you can modify this into list of servers from a group)
- gets list of windows servers from gateway server (we use gateway servers but you can modify this into list of servers from a group)
- run SQL query to extract performance data form the data warehouse (for the list of servers specified above and store the results)
- analyze the each performance counter/instance and workout the average and projections (The store these results for reporting)
- clean the data table (remove negative numbers and replace with 0)
- generate html reports based on the processed data
- 0-45 days to upgrade (report lists CPU/Memory/Disks that will run out of space in the next 45 days or have already run out of space)
- 46-200 days to upgrade (report lists CPU/Memory/Disks that will run out of space in the next 46-200 days or have already run out of space)
- Full table of results for reference
The report is then scheduled on a management server and is only run once per month.
Note: It takes 6 hours to process as this is a synchronous script and depends on the number of servers or objects that need to be analysed. (some unix servers have upwards of 30 file systems which causes the script to take some time.
I originally had this all in one script but to speed up processing I split it into 2 (Windows and Unix).
I also investigated power shell workflows but it has it limitations as it wont use some of the power shell commands I had in my script.
Download Capacity Report Script Here
Edit: I have moved this to github: https://github.com/Buzzcola81/scom2012forecasting
Please contact me if you have any questions.
Susta's Log: Sharing my experience in IT 
Hi Martin, I’m trying your script on my SCOM 2012R2 Data Warehouse server, but I’m getting the following errors:
Script started 08/04/2015 14:23:33
Exception calling “ExecuteReader” with “0” argument(s): “Incorrect syntax near ‘)’.”
At D:\Reports\Windows_Capacity_Projection_Report.ps1:71 char:5
+ $Reader = $cmd.ExecuteReader()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Exception calling “Load” with “1” argument(s): “Value cannot be null.
Parameter name: dataReader”
At D:\Reports\Windows_Capacity_Projection_Report.ps1:74 char:5
+ $Datatable.Load($Reader)
+ ~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ArgumentNullException
Query completed – 08/04/2015 14:23:33
Raw Data saved – 08/04/2015 14:23:33
Import data – completed 08/04/2015 14:23:33
Import data – memory data cleaned 08/04/2015 14:23:33
Import data – serverlist gathered 08/04/2015 14:23:33
Analyze data – completed 08/04/2015 14:23:33
Any idea what is going on? To the best of my knowledge, I’ve changed all of the fileshare and server information.
Thanks,
Bill
Hi Bill,
This line is what gets the list of windows servers.
$WindowsMembers = Get-SCOMGatewayManagementServer -Name “$GatewayServerUsed*”| Get-SCOMAgent | select DisplayName
All you would need to to is update $WindowsMembers to query the group and only report on the window servers.
Something like
Get-SCOMGroup -DisplayName “GROUPNAME” | Get-SCOMClassInstance | sort DisplayName |FT DisplayName
Thanks Martin.
Yes, I found that before you replied and had it exactly right. I edited the $WindowMembers line, and I don’t get any errors when I run it. The script takes a while to run, but my .CSV files are still empty 0 byte files. I’ll look at permissions. I’ll keep trying. I used the “Windows Server Computer Group” as the group name.
Thanks again,
Bill
I tried $WindowsMembers = Get-SCOMGroup -DisplayName “Windows Server Computer Group” | Get-SCOMClassInstance | sort DisplayName |FT DisplayName but it still didn’t work. I’ll try again when I get more time.
I don’t have a gateway server in our environment, but you suggest using a group. I tried adding a scom group name to the $GatewayServerUsed variable, but get the same error. Can you be more specific in how this is done?
Thanks,
Bill
I figured it out I think. It’s running right now. I was trying to run it on our Datawarehouse server where the SQL database is. I ran just a couple lines of code to see if it would list the servers but it came up with an error that the Data Access service is not running. I moved the script to one of the management servers where the Data Access service is running and it looks like it’s working. I’l know more in a little while.
I got an error some error about” Script started 10/26/2015 14:49:54
Exception calling “ExecuteReader” with “0” argument(s): “Incorrect syntax near the keyword ‘AND’.”
At E:\XXXXX\Windows_Capacity_Projection_Report.ps1:68 char:33
+ $Reader = $cmd.ExecuteReader <<<< ()
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
Exception calling "Load" with "1" argument(s): "Value cannot be null.
Parameter name: dataReader"
At E:\XXXXX\Windows_Capacity_Projection_Report.ps1:71 char:20
+ $Datatable.Load <<<< ($Reader)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
Query completed – 10/26/2015 14:49:56
"
Please help me if you could!
Thank you,
Still got error below:
Script started 10/26/2015 16:40:17
Query completed – 10/26/2015 16:40:35
Export-Csv : Cannot bind argument to parameter ‘InputObject’ because it is null.
At E:\XXXX\Windows_Capacity_Projection_Report.ps1:150 char:29
+ $sqlresults | export-csv <<<< $tablecsvpathwithdate -notypeinformation
+ CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCo
mmand
Raw Data saved – 10/26/2015 16:40:35
Import data – completed 10/26/2015 16:40:35
Import data – memory data cleaned 10/26/2015 16:40:35
Import data – serverlist gathered 10/26/2015 16:40:35
Trending data – and – Instance number is 1 – 10/26/2015 16:40:35
Please help!
Hi Rattha,
Ill investigate and get back to you.
Hi Martin,
My environment has only Windows servers and does not have GatewayServer, so in your script I changed 2 variables:
$GatewayServerUsed = “Operations Manager Agents”
$ResourcePoolUsed = “Operations Manager Agents”
Also, I used this:
$WindowsMembers = Get-SCOMGroup -DisplayName “Operations Manager Agents” | Get-SCOMClassInstance | sort DisplayName | FT DisplayName
Then, the script run with the same error: “Export-Csv : Cannot bind argument to parameter ‘InputObject’ because it is null.”
I noticed that the script created a file named “WinRawDataCapacity-151028094718.csv”, but there is no data on the file.
What do you advice me? Please help me!
Apologies not getting back to you sooner, I have been flat-out at work and at home last few days. Please use the contact me Tab so we can converse via email.
Thanks Martin.
Love the look of the script but I am stuck at the same place as you guys…
Are you trying to run the report against a group of servers or against a resource pool?
A group of servers …Do they need to be Windows objects or would Ops mgr Agent do ??
Ahh I designed the script to get the list of server reporting to a resourcepool (for unix servers) and gateway server for windows servers.
You will need to modify the script to cater for groups, see previous comments from Rattha for how it was done.
A tweak for newer management packs, due to recent changes in management packs this line in the script needs this line changed from :
(vPerformanceRule.ObjectName IN (‘Memory’, ‘LogicalDisk’, ‘Processor’))
to :
(vPerformanceRule.ObjectName IN (‘Memory’, ‘LogicalDisk’, ‘Processor Information’))
Bingo you have data…
Hi,
i find this but i have a problem during importdata, i gave this error
Starting Script 05/13/2016 17:25:20
Query completed – 05/13/2016 17:25:39
Raw Data saved – 05/13/2016 17:25:48
Import data – completed 05/13/2016 17:25:57
Import data – memory data cleaned 05/13/2016 17:26:04
Import data – serverlist gathered 05/13/2016 17:27:28
Trending data – @{Path=SERVER01} and @{InstanceName=0} – Instance numb
er is 1 – 05/13/2016 17:27:47
Unable to convert value «4,35529579306549» in type «System.Int32».
Error: «Input string was not in a correct format.»
Au caractère C:\Report\Script\Windows_Capacity_Projection_Report.ps1:191 : 9
+ $sumXY+=($i)*($data[$i-1])
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument : (:) [], RuntimeException
+ FullyQualifiedErrorId : InvalidCastFromStringToInteger
i don”t know how solve this problem
Have a look at the data, it may not be an int ie it might contain a ‘,’ so it might look like a string thus causing error when trying to get calculated.
Once you identify the data you could look at filtering it out (i had a similar issue with memory data where i had to cleanse it so I could calculate it later)
thanks you so much for your answer.
i ll check csv file so as to see if we have a data problem.
Stephane
Hi,
i checked csv file, but all seems ok.
it will be possible the problem comes from the datetme
i have this one in my file
31/08/2016 22:00:00,”76,6209665981328″,””,”SERVER”,”Memory”,”PercentMemory
the datetime is not between in””.
it could cause my error ?
Stephane
Or the value need to have “.” instead of “;”
Hi Martin,
This is a great script. The reports look great. Thank you. can you elaborate a bit on how you do the forecasting calculations?
Thanks,
Hector
Hi Hector,
Thanks for your feedback. As for the forecasting calculations i used the functions found from gallery.technet.microsoft.com as this generates a simple linear trend. I use this to then determine what will has/will breach 90% in the next 0-45 days and then 46-200 days.
This is useful to attempt to predict what potential performance/space issues might be happening soon before we get an alert.
Hope this helps.
Thanks Martin.
Thanks Martin. The reports are outstanding. I have been running the reports against my DW DB then sampling a few server’s trending/forecast with excel data mining and re results are identical for the most part. great work.
I’ve been trying to replace the forecasting to 15, 30 and 45 days. I thought I replaced the all inside the script but I’m missing something. the Capacity Data Report’s projections come out blank… can you suggest what to change to reflect above projections? thanks…
Hi Hector,
The following lines are where you need to modify the days:
[string]$A90Days = (90 * $rate) + $Average
[string]$A180Days = (180 * $rate) + $Average
[string]$A365Days = (365 * $rate) + $Average
to:
[string]$A90Days = (15 * $rate) + $Average
[string]$A180Days = (30 * $rate) + $Average
[string]$A365Days = (45 * $rate) + $Average
Don’t modify the variable names (if you just want it to work with minimal changes)
Then go to the “#build HTML Body for report Capacity Provisioning” section and update the reports HTML output for the table names (probably worth just making the above change and seeing if the reports are ok then updating the reports html)
Hope this helps.
Thanks Martin.
Thanks Martin. That worked. Thanks. I Just changed the variable values.
One thing I noticed on my end, not sure if this has affected you, is that while sampling the data, many forecast values were trending the opposite way. Meaning, in the case of a heavily utilized CPU, its 45 day forecast was decreasing and trending into negative values, the opposite when those values were presented in a linear graph.
For me, that was caused by the fact the SQL query in the script sorts DateTime in a Descending manner. If a resource is increasing in usage, which is often the case, the forecast will show a decreasing trend because the older date is a the bottom of the table.
Anyway, removing the DESC resolved my issue.
Thanks, this is what i was exactly looking for. I already had the Volume utilization report using SQL. But this is perfect, as it does create Report for Volume, disk and CPU automatically.
But I am not able to understand why % Free Space is increasing for projections on Disks. It should be decreasing not increasing.
Hi harmikbatth, Was this for unix or windows? From memory the projection is based on 30 day window and is just a linear projection function also from meory one is % free and the other is % used.
Thanks Martin. It is for windows.
Disk counter used is %Free space, projections shows like this
Disk Utilization Capacity Projections
Server Name Counter Instance Name Average for period ‘%’ Projection 90 Days Projection 180 Days Projection 365 Days
Svr A % Free Space C: 87.81 88.93 90.05 92.36
Svr A % Free Space C: 86.4 86.59 86.78 87.18
Svr A % Free Space D: 89.55 89.55 89.55 89.55
Svr A % Free Space C: 80.99 82.11 83.23 85.54
Svr A % Free Space E: 95.45 96.1 96.75 98.07
Svr A % Free Space D: 99.38 99.32 99.27 99.15
As you can see Projections for % Free space is increasing for 91, 180 and 365 days. Where it should be decreasing trend.
Although I think i have fixed it via changing the code.
Before
—————————–
$A90Days = (90 * $rate) + $Average
$A180Days = (180 * $rate) + $Average
$A365Days = (365 * $rate) + $Average
After
—————————–
if ($CounterName -eq “% Free Space”)
{
if ([string]$A90Days -ge 0) { $A90Days = $Average – (90 * $rate) } Else { $A90Days = (90 * $rate) + $Average }
if ([string]$A180Days -ge 0) { $A180Days = $Average – (180 * $rate) } Else { $A180Days = (180 * $rate) + $Average }
if ([string]$A365Days -ge 0) { $A365Days = $Average – (365 * $rate) } Else { $A365Days = (365 * $rate) + $Average }
#Write-host $Countername “Negative” $rate $Average $A90Days $A180Days $A365Days
}
Else
{
$A90Days = (90 * $rate) + $Average
$A180Days = (180 * $rate) + $Average
$A365Days = (365 * $rate) + $Average
#Write-host $Countername “Positive” $rate $Average $A90Days $A180Days $A365Days
}
This has allowed me to decrease instead of increase. I hope this is what it should be.
I am also bit confused about Predictions for CPU and memory utilization.
Hi Martin,
I’ve executed the code on my SCOM 2012 R2 Management Server (also has SQL dedicated 2008 R2 DB) but it executes in about 1 second with NO errors.
The script is being executed with SQL Admin privileges to the SCOM DW.
Query completed – 03/09/2017 12:12:40
Raw Data saved – 03/09/2017 12:12:40
Import data – completed 03/09/2017 12:12:40
Import data – memory data cleaned 03/09/2017 12:12:40
Import data – serverlist gathered 03/09/2017 12:12:40
Analyize data – completed 03/09/2017 12:12:40
Debug wise, the command below outputs my list of agent managed servers:
Get-SCOMGroup -DisplayName “Windows Server Computer Group” | Get-SCOMClassInstance | sort DisplayName |FT DisplayName
As represented in your code as:
$WindowsMembers = Get-SCOMGroup -DisplayName “Windows Server Computer Group” | Get-SCOMClassInstance | sort DisplayName |FT DisplayName
The archive folder and save folder must be different folders as I was receiving “process in use errors”.
$ArchiveFolder = “C:\TrendingReport\Windows”
$ReportSaveFolder = “\\scom_server\SCOM Scheduled Folder”
The script is being executed from within PowerShell ISE for testing.
A scheduled task has also been created:
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe
-ExecutionPolicy bypass C:\Admin\TrendingReport\trendscript.ps1 -RunTpe $tru -Path c:\admin\TrendingReport
Help most appreciated!
Hi Martin,
Your script is now processing trending data. I made the following change:
$WindowsMembers = Get-ScomAgent -ComputerName “<enter your SCOM servers name" | Select DisplayName
Thanks for creating a great script.
Steve
Thank you and nice work. 🙂
Hi,
The script looks awesome, I would really like to use it but I am currently getting the following error.
Cannot convert value “90,1811616685655” to type “System.Int32”. Error: “Input string was not in a correct format.
Not sure if I am perhaps doing something wrong
Hi Leon,
Please see my previous comments on another users same issue. Might be the data being extracted.
Unfortunately I no longer work on SCOM so have no way of testing thus no longer support this solution.
Regards,
Martin