Archief categorieën: Tech

N-Able Report Manager – Adjustable Missing Patches in Report

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

1 2	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)

<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>

<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>

<VisibilityParameter>SecMon_WinPatchAdvSet</VisibilityParameter>

</ValidationGroup>

</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)

<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>

<ParameterName>SecMon_WinPatchNoMissing</ParameterName>

<DataType>Integer</DataType>

<UserPrompt>Number of missing patches</UserPrompt>

<HintHelp>Assign a number of missing patches for score 0</HintHelp>

<VisibilityParameter>SecMon_WinPatchAdvSet</VisibilityParameter>

</ValidationGroup>

</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">

1	<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>

<DataType>Integer</DataType>

</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>

<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)

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 &lt;= @UpdatedEndDate 
			AND ps.CustomerID in (@CustomerList)
			AND (ps.EndDate &gt; @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 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;

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 &lt;= @UpdatedEndDate 
	AND ps.CustomerID in (@CustomerList)
	AND (ps.EndDate &gt; @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

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

1 2	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.

Message tracking in Microsoft Exchange Server 2013

Geplaatst door Martien Wijnands op juni 19, 2014 Geen reacties

Helaas hebben we met de komst van Exchange 2013 ook afscheid moeten nemen van de message tracking log functie in Exchange.

Het Message Tracking Center in Exchange 2003 is natuurlijk de moeder der trackers geweest, sinds dien is het alleen maar minder bruikbaar geworden, helaas!

In Microsoft Exchange 2013 ECP is er wel een mogelijkheid tot bekijken van bezorgrapporten, maar helaas dient men hier een mailbox op te geven waarin gezocht wordt.

Geen nood! Met powershell komt men een heel eind.

Get-MessageTrackingLog

1	Get-MessageTrackingLog

Informatie Get-MessageTrackingLog

Als men deze opdracht uitvoert wordt het Message Tracking Log gedumpt in de console, precies waar we naar op zoek zijn, maar niet in de vorm die we willen hebben. Zo staat bijvoorbeeld het MessageID niet vermeld en de datum/tijd niet.

Get-MessageTrackingLog | Select-Object
Om de velden die we wensen erbij te krijgen maken we gebruik van de optie Select-Object
Hiermee kunnen we de gewenste velden erbij selecteren.

Get-MessageTrackingLog | Select-Object&nbsp;eventid,timestamp,messageid,sender,recipients,messagesubject

1	Get-MessageTrackingLog \| Select-Object eventid,timestamp,messageid,sender,recipients,messagesubject

óf voor een hele uitgebreide output

Get-MessageTrackingLog | Select-Object *

1	Get-MessageTrackingLog \| Select-Object *

Get-MessageTrackingLog | Select-Object * | Out-GridView

1	Get-MessageTrackingLog \| Select-Object * \| Out-GridView

De Out-GridView optie is geweldig voor output die te groot is naar een console, filtering en sortering kan hier direct in gebeuren.

De output van bovenstaand comando ziet er dan ongeveer zo uit;

Andere varianten en filteropties;

Standaard geeft het Get-MessageTrackingLog commando de laatste paar honderd logregels weer, niet altijd wenselijk.

Get-MessageTrackingLog -Start “Jun 19 2014” | select-object *| out-gridview
Gebruik -Start “MMM DD YYYY” om een bepaalde startdatum op te geven.
Gebruik -End “MMM DD YYYY” om een bepaalde stopdatum op te geven.

Zelfs met de start en eind datum ingevuld is de output beperkt tot een aantal regels, om deze beperking op te heffen gebruik je -ResultSize Unlimited

Mijn favoriet;

get-messagetrackinglog -Start "Jun 1 2014" -End "Jun 2 2014" -ResultSize Unlimited | select-object eventid,timestamp,messageid,sender,recipients,messagesubject | out-gridview

1	get-messagetrackinglog -Start "Jun 1 2014" -End "Jun 2 2014" -ResultSize Unlimited \| select-object eventid,timestamp,messageid,sender,recipients,messagesubject \| out-gridview

WinSXS map kleiner maken in Windows 7 SP1

Geplaatst door Martien Wijnands op maart 7, 2012 Geen reacties

Al een tijdje ergerde ik mij aan de grootte van de C:\Windows\WinSXS map.
Sinds de installatie van mijn Microsoft Windows 7 machine in Maart 2010 is deze map alleen maar blijven groeien. Twee jaar en 12,1 GB later was ik het zat. Ik moest een manier vinden om de WinSXS map te verkleinen.

Uit verschillende artikelen blijkt dat de WinSXS map niet zomaar verwijderd mag worden, het is een belangrijk onderdeel van Windows 7, zelfs zo belangrijk dat bij verwijderen je installatie kapot is.
Opruimen dan maar! Enige voorzichtigheid is echter wel geboden bij het uitvoeren van dit commando. Het ongedaan maken (de-installeren) van SP1, is per direct niet meer mogelijk na het uitvoeren van deze opdracht.

Start een elevated command promt en voer de volgende opdracht uit:

dism /online /cleanup-image /spsuperseded

Voor:

Tijdens:

Na:

Scheelde in mijn geval toch een grove 5GB aan schijfruimte.

Zorg er altijd voor dat je een volledige backup van je systeem hebt, mocht je niet weten wat je aan het doen bent, vraag dan een professional je te helpen met deze kwestie. Ik neem dan ook geen enkele verantwoordelijkheid voor de door mij aangeboden oplossingen/tips/opdrachten etc.

Knifto.nl

Knifto'ed Mind!

Archief categorieën: Tech

N-Able Report Manager – Adjustable Missing Patches in Report

Download Files from your Report Manager Server

Edit the XML file

Edit the RDL file

Background information

Message tracking in Microsoft Exchange Server 2013

WinSXS map kleiner maken in Windows 7 SP1

Categorieën