I’m using N-Able for a few months now, my clients were asking for some reports so I decided to take a look at the N-Able Report Manger.
One of the main disadvantages for the reports were the Windows Patch Status Scores.
Apparently, if your machine is missing just one patch, it scores 0%
After hours of ‘reverse engineering’ I get what I wanted. A custom report based on the standard ‘Executive Report’, with adustable ‘missing patches’.
Now I can choose what the number of patches missing is giving the machine a score of 0%.
If you want to follow this little turorial, you need to know something about custom reports.
I will write this tutorial based on the default Executive Report. I’m using N-Able Report Manager 4.3 SP2
Download Files from your Report Manager Server
We will create a ‘Custom Report’ in the system, this requires some files.
Log in to your Report Manger Server (RDP) and copy these two files to another location.
|
|
C:\Program Files (x86)\N-able Technologies\Report Manager\reports\Stock Reports\Executive Summary Report Parameters.xml C:\Program Files (x86)\N-able Technologies\Report Manager\reports\Stock Reports\Executive Summary Report.rdl |
The RDL file, contains the Report itself, the XML file contains the settings for the Report.
Just copy the files from the original directory, so you can edit them (and keep a copy of the original files!)
Edit the XML file
First we need to edit the XML file. The XML file contains the settings and choises for your Report.
Open the XML file in your favorite editor and look for the following text (line 1055)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<RMParameter> <ParameterName>SecMon_WinPatchWeight</ParameterName> <DataType>Integer</DataType> <UserPrompt>Weight the Score:</UserPrompt> <HintHelp>Assign a weight which will determine the contribution of this section to the Overall Score.</HintHelp> <HasDefaultValue>1</HasDefaultValue> <DefaultValue>50</DefaultValue> <AvailableValues>0;100;5</AvailableValues> <AllowNulls>0</AllowNulls> <AllowBlank>0</AllowBlank> <MultiValues>0</MultiValues> <Hidden>0</Hidden> <ControlID>26</ControlID> <ParentParameterID>-1</ParentParameterID> <DisplayOrder>50</DisplayOrder> <VisibilityParameter>SecMon_WinPatchAdvSet</VisibilityParameter> <ValidationGroup> </ValidationGroup> <InsertLineAbove>0</InsertLineAbove> </RMParameter> |
As you can see, every parameter is starting with <RMParameter> and ending with </RMParameter>
After this parameter you must add the following text (from line 1075)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<RMParameter> <ParameterName>SecMon_WinPatchNoMissing</ParameterName> <DataType>Integer</DataType> <UserPrompt>Number of missing patches</UserPrompt> <HintHelp>Assign a number of missing patches for score 0</HintHelp> <HasDefaultValue>1</HasDefaultValue> <DefaultValue>30</DefaultValue> <AvailableValues>0;100;5</AvailableValues> <AllowNulls>0</AllowNulls> <AllowBlank>0</AllowBlank> <MultiValues>0</MultiValues> <Hidden>0</Hidden> <ControlID>26</ControlID> <ParentParameterID>-1</ParentParameterID> <DisplayOrder>50</DisplayOrder> <VisibilityParameter>SecMon_WinPatchAdvSet</VisibilityParameter> <ValidationGroup> </ValidationGroup> <InsertLineAbove>0</InsertLineAbove> </RMParameter> |
Now, we defined a new Parameter. This will let you choose the number of missing patches before running the Report.
If you want to adjust the default value, just change the number on <DefaultValue>
Save the file.
Hold on! First we need to edit the RDL file (the funpart!)
Edit the RDL file
Open the RDL file in you favorite editor (I use notepad++)
The RDL file is a XML file, in notepad++ you can collapse and expand the datasets.
First, find the part where all the Report Parameters are defined (bottom) and look for: (line 86934)
|
|
<ReportParameter Name="SecMon_WinPatchWeight"> |
After this parameter, you need to add your parameter:
(so you will add this text AFTER </Reportparameter> (line 86942)
|
|
<ReportParameter Name="SecMon_WinPatchNoMissing"> <DataType>Integer</DataType> <DefaultValue> <Values> <Value>50</Value> </Values> </DefaultValue> <Prompt>Number of missing patches:</Prompt> </ReportParameter> |
Next, we need to change some datasets.
If you collapse the datasets in your editor you can easily manage the datasets
Expand the dataset named ‘dsWinPatchScore’
First we need to add the Query parameter
Add the following code to the QueryParameters
|
|
<QueryParameter Name="@SecMon_WinPatchNoMissing"> <Value>=Parameters!SecMon_WinPatchNoMissing.Value</Value> </QueryParameter> |
You will find a SQL code within this dataset.
Find the following SQL code (line 3322 to 3340)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT distinct ps.DeviceID INTO #OutdatedDevices FROM fact_PatchStatus ps INNER JOIN dim_Patches p ON p.PatchID = ps.PatchID INNER JOIN dim_PatchCategory pct ON pct.CategoryID = p.CategoryID AND pct.CategoryName NOT IN ('3rd Party', 'Third Party') LEFT JOIN #ManagedDevice md ON md.DeviceID = ps.DeviceID WHERE ps.StartDate <= @UpdatedEndDate AND ps.CustomerID in (@CustomerList) AND (ps.EndDate > @UpdatedEndDate OR ps.EndDate IS NULL) AND ps.InstallationStatusID NOT IN (104, 105) AND ps.InstallationStatusID is not null AND ((ps.ApprovalStatusID in (SELECT Value from warehouse.dbo.inline_split_me(@ApprovalStatus))) OR (md.DeviceID IS NULL AND @SecMon_WinPatchNotMan = 1)) AND ((ps.IsMonitored = 1 AND @SecMon_WinPatchMonCat = 0) OR @SecMon_WinPatchMonCat = 1) AND ((md.DeviceID IS NOT NULL AND @SecMon_WinPatchNotMan = 0) OR @SecMon_WinPatchNotMan = 1) |
And REPLACE this code with the following code;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SELECT ps.DeviceID INTO #OutdatedDevices2 FROM fact_PatchStatus ps INNER JOIN dim_Patches p ON p.PatchID = ps.PatchID INNER JOIN dim_PatchCategory pct ON pct.CategoryID = p.CategoryID AND pct.CategoryName NOT IN ('3rd Party', 'Third Party') LEFT JOIN #ManagedDevice md ON md.DeviceID = ps.DeviceID WHERE ps.StartDate <= @UpdatedEndDate AND ps.CustomerID in (@CustomerList) AND (ps.EndDate > @UpdatedEndDate OR ps.EndDate IS NULL) AND ps.InstallationStatusID NOT IN (104, 105) AND ps.InstallationStatusID is not null AND ((ps.ApprovalStatusID in (SELECT Value from warehouse.dbo.inline_split_me(@ApprovalStatus))) OR (md.DeviceID IS NULL AND @SecMon_WinPatchNotMan = 1)) AND ((ps.IsMonitored = 1 AND @SecMon_WinPatchMonCat = 0) OR @SecMon_WinPatchMonCat = 1) AND ((md.DeviceID IS NOT NULL AND @SecMon_WinPatchNotMan = 0) OR @SecMon_WinPatchNotMan = 1) SELECT DeviceID INTO #OutdatedDevices FROM #OutdatedDevices2 GROUP BY deviceid HAVING COUNT (deviceid)>@SecMon_WinPatchNoMissing |
Also add the following code before END
|
|
IF (OBJECT_ID('tempdb..#OutdatedDevices2') IS NOT NULL) DROP TABLE #OutdatedDevices2 |
I made a couple of changes in the SQL code;
– Removed the Distinct feature on the SELECT statement
– Put the Result of first SELECT statement in #OutdatedDevices2
I created a second SELECT statement which will put all records bigger than your selected missing patches into the original #OutdatedDevices table.
And make sure the #OutdatedDevices2 is cleaned up
You need to make this changes on the following datasets;
dsWinPatchMonitoredCurrent
dsWinPatchScore
dsWinPatchScoreP
Save your file and upload the file to your Report Manger (along with the XML configuration file) as a custom report.
Enjoy!
Background information
By default, the report manager is gathering data about machines with missing patches, put them in a temporary table and counts the number of machines.
I made a second step in this process, first I will put all the missing patches (with their machine ID’s) in a temporary table. Next, I will put the machines missing X patches in the (original) temporary table.
