script

Automate your SUS clean up

Many companies rely on WSUS respective SUS services from Microsoft – aka. Windows Server Update Services as internal source and control of their update deployment to clients and servers within their network.

One of the big challenges for IT is to keep them clean and performant. The Cleanup-Assistant in the SUS management console tends to run forever and in any case means manual labor over and over again.

Below are two scripts – a CMD script that needs to be adjusted with parameters and a powershell script that will be called with those parameters. The scripts acutally will call the same API as the MMC Assistant does, just that this can be automatically performed via a scheduled task in Windows.

It helps you to keep your SUS slim and more performant.

In any way – I highly recommend to not blindly just enable all categories rather then limiting it to the once you have in place as well as once you reached a certain patch-level even actively denying updates you never will need again (keep in mind, new rolled out systems might still need older updates – but you could possibly refresh your base images or rely on Microsoft update services / online updates for those cases).

The combination of making updates obsolete and actually running a cleanup periodically will improve your SUS server performance.

As for the parameters, those are explained in the CMD script header – therefor I will not explain them here again.

@echo off 
REM SUS - CleanUp Script
REM Script start a Windows PowerShell Script that will acces the WSUS API and start a cleanup.
REM All parameters are set in this file, there is no need to change the PS script.
REM An email with results will be generated as well.

REM ScriptPath: Path where this and the PS1 script resides
REM Log: name of the logfile / shouldn't need to be adjusted
REM PSLog: name of PS script logfile, shouldn't need to be adjusted
REM CleanupScript: name of the PS1 script - you shouldn't need to adjust this

REM SUSServerFQDN: FQDN/full DNS name of the SUS Server (hostname.dns.tld)
REM SUSServerPort: Port-Number (80 / 443 / 8530 / 8531 - depending on your installation, check IIS if necessary)
REM SUSContentDrive: Drive-Letter of the partition that holds the WSUS-Content ( C or D or E - without the : )

REM supersededUpdates: 1 or 0 => Decline updates that have not been approved for 30 days or more, are not currently needed by any clients, and are superseded by an aproved update.
REM expiredUpdates: 1 or 0 => Decline updates that aren't approved and have been expired my Microsoft.
REM obsoleteUpdates: 1 or 0 => Delete updates that are expired and have not been approved for 30 days or more.
REM compressUpdates: 1 or 0 => Delete older update revisions that have not been approved for 30 days or more.
REM obsoleteComputers: 1 or 0 => Delete computers that have not contacted the server in 30 days or more.
REM unneededContentFiles: 1 or 0 => Delete update files that aren't needed by updates or downstream servers.

REM SMTPServer: DNS-Name of the Mail-Server (hostname.dns.tld)
REM MailFrom: Mail-Sender address
REM MailTo: Mail-Recipient address


REM ****** BEGIN Parameter Configuration ****** 
Set ScriptPath=C:\Batch\SUSCleanUp
Set Log=%ScriptPath%\Logs\wsus-cleanup.log 
Set PSLog=%ScriptPath%\Logs\ps-cleanup.log 
Set CleanupScript=%ScriptPath%\SUSCleanUp.ps1

Set SUSServerFQDN=our-sus-server.domain.local
Set SUSServerPort=8530
Set SUSContentDrive=D

Set supersededUpdates=1
Set expiredUpdates=1
Set obsoleteUpdates=1
Set compressUpdates=1
Set obsoleteComputers=1
Set unneededContentFiles=1

Set SMTPServer=relay.domain.com
Set MailFrom=administrator@domain.com
Set MailTo=suslogfiles@domain.com
REM ****** END Paremeter Configuration ******


REM CleanUp and PreRequirements
MD %ScriptPath%\Logs 1>NUL 2>NUL
REM DEL %Log% 1>NUL 2>NUL
DEL %PSLog% 1>NUL 2>NUL

REM Script Execution
echo. >> "%log%" 
echo ############################################################ >> "%log%" 
echo. >> "%log%"
echo Begin Cleanup Report %wsus-server-01% on %date% %time%: >> "%log%" 
echo ------------------------------------------------------------ >> "%log%" 
"%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe" "%cleanupscript% '%susserverfqdn%' '%pslog%' '%smtpserver%' '%mailfrom%' '%mailto%'" %supersededUpdates% %expiredUpdates% %obsoleteUpdates% %compressUpdates% %obsoleteComputers% %unneededContentFiles% %SUSContentDrive% %SUSServerPort% >> "%log%"
echo ------------------------------------------------------------ >> "%log%"
echo End Cleanup Report %wsus-server-01% on %date% %time%: >> "%log%"

@echo off

REM SUS - CleanUp Script

REM Script start a Windows PowerShell Script that will acces the WSUS API and start a cleanup.

REM All parameters are set in this file, there is no need to change the PS script.

REM An email with results will be generated as well.

REM ScriptPath: Path where this and the PS1 script resides

REM Log: name of the logfile / shouldn't need to be adjusted

REM PSLog: name of PS script logfile, shouldn't need to be adjusted

REM CleanupScript: name of the PS1 script - you shouldn't need to adjust this

REM SUSServerFQDN: FQDN/full DNS name of the SUS Server (hostname.dns.tld)

REM SUSServerPort: Port-Number (80 / 443 / 8530 / 8531 - depending on your installation, check IIS if necessary)

REM SUSContentDrive: Drive-Letter of the partition that holds the WSUS-Content ( C or D or E - without the : )

REM supersededUpdates: 1 or 0 => Decline updates that have not been approved for 30 days or more, are not currently needed by any clients, and are superseded by an aproved update.

REM expiredUpdates: 1 or 0 => Decline updates that aren't approved and have been expired my Microsoft.

REM obsoleteUpdates: 1 or 0 => Delete updates that are expired and have not been approved for 30 days or more.

REM compressUpdates: 1 or 0 => Delete older update revisions that have not been approved for 30 days or more.

REM obsoleteComputers: 1 or 0 => Delete computers that have not contacted the server in 30 days or more.

REM unneededContentFiles: 1 or 0 => Delete update files that aren't needed by updates or downstream servers.

REM SMTPServer: DNS-Name of the Mail-Server (hostname.dns.tld)

REM MailFrom: Mail-Sender address

REM MailTo: Mail-Recipient address

REM ****** BEGIN Parameter Configuration ******

Set ScriptPath=C:\Batch\SUSCleanUp

Set Log=%ScriptPath%\Logs\wsus-cleanup.log

Set PSLog=%ScriptPath%\Logs\ps-cleanup.log

Set CleanupScript=%ScriptPath%\SUSCleanUp.ps1

Set SUSServerFQDN=our-sus-server.domain.local

Set SUSServerPort=8530

Set SUSContentDrive=D

Set supersededUpdates=1

Set expiredUpdates=1

Set obsoleteUpdates=1

Set compressUpdates=1

Set obsoleteComputers=1

Set unneededContentFiles=1

Set SMTPServer=relay.domain.com

Set MailFrom=administrator@domain.com

Set MailTo=suslogfiles@domain.com

REM ****** END Paremeter Configuration ******

REM CleanUp and PreRequirements

MD %ScriptPath%\Logs 1>NUL 2>NUL

REM DEL %Log% 1>NUL 2>NUL

DEL %PSLog% 1>NUL 2>NUL

REM Script Execution

echo. >> "%log%"

echo ############################################################ >> "%log%"

echo. >> "%log%"

echo Begin Cleanup Report %wsus-server-01% on %date% %time%: >> "%log%"

echo ------------------------------------------------------------ >> "%log%"

"%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe" "%cleanupscript% '%susserverfqdn%' '%pslog%' '%smtpserver%' '%mailfrom%' '%mailto%'" %supersededUpdates% %expiredUpdates% %obsoleteUpdates% %compressUpdates% %obsoleteComputers% %unneededContentFiles% %SUSContentDrive% %SUSServerPort% >> "%log%"

echo ------------------------------------------------------------ >> "%log%"

echo End Cleanup Report %wsus-server-01% on %date% %time%: >> "%log%"

# WSUS Connection Parameters:
[String]$WSUSServer = $args[0] 
[Boolean]$useSecureConnection = $False
[Int32]$portNumber = $args[12]
[String]$LogFile = $args[1] 
[String]$SMTPServer = $args[2] 
[String]$MailFrom = $args[3]
[String]$MailTO = $args[4]
[String]$SUSDrive = $args[11]
[String]$WMIDiskSpaceRequest = "\\" + $WSUSServer + "\root\cimv2:Win32_logicalDisk.DeviceID='" + $SUSDrive + ":'"

# Windows PowerShell example to check 'If File Exists'
#$FileExists = Test-Path $LogFile
#If ($FileExists -eq $True) {
#Delete old LogFiles
# Remove-Item $LogFile
#}

# Cleanup Parameters:
# Decline updates that have not been approved for 30 days or more, are not currently needed by any clients, and are superseded by an aproved update.
[Boolean]$supersededUpdates = $True
If ($args[5] = 0) {
$supersededUpdates = $False
}
# Decline updates that aren't approved and have been expired my Microsoft.
[Boolean]$expiredUpdates = $True
If ($args[6] = 0) {
$expiredUpdates = $False
}
# Delete updates that are expired and have not been approved for 30 days or more.
[Boolean]$obsoleteUpdates = $True
If ($args[7] = 0) {
$obsoleteUpdates = $False
}
# Delete older update revisions that have not been approved for 30 days or more.
[Boolean]$compressUpdates = $True
If ($args[8] = 0) {
$compressUpdates = $False
}
# Delete computers that have not contacted the server in 30 days or more.
[Boolean]$obsoleteComputers = $True
If ($args[9] = 0) {
$obsoleteComputers = $False
}
# Delete update files that aren't needed by updates or downstream servers.
[Boolean]$unneededContentFiles = $True
If ($args[10] = 0) {
$unneededContentFiles = $False
}
#EndRegion VARIABLES

#Region SCRIPT
# Load .NET assembly
[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration");

# Connect to WSUS Server
$wsusParent = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($WSUSServer,$useSecureConnection,$portNumber);

# Log the date first
"Microsoft SUS Server CleanUp" | out-file -filepath $LogFile -append -noClobber;
$date = date
$info = "Start Date / Time: " + $date
$info | out-file -filepath $LogFile -append -noClobber;

# Log the SUS Server Name
$info = "Microsoft SUS Server Name: " + $WSUSServer
$info | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

# Log Current Free DiskSpace
$diskbefore = ([wmi]$WMIDiskSpaceRequest)
"Free disk space before cleanup:" | out-file -filepath $LogFile -append -noClobber;
"Drive " + $SUSDrive + ": has {0:#.0} GB free of {1:#.0} GB Total" -f ($diskbefore.FreeSpace/1GB),($diskbefore.Size/1GB) | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

# Perform Cleanup
"Microsoft SUS Server CleanUp Results" | out-file -filepath $LogFile -append -noClobber;
"====================================" | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

$CleanupManager = $wsusParent.GetCleanupManager();

"CleanUp: supersededUpdates = " + $supersededUpdates | out-file -filepath $LogFile -append -noClobber;
"----------------------------------" | out-file -filepath $LogFile -append -noClobber;
date | out-file -filepath $LogFile -append -noClobber;
$CleanupScope_supersededUpdates = New-Object Microsoft.UpdateServices.Administration.CleanupScope($supersededUpdates,$False,$False,$False,$False,$False);
$CleanupManager.PerformCleanup($CleanupScope_supersededUpdates) | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: expiredUpdates = " + $expiredUpdates | out-file -filepath $LogFile -append -noClobber;
"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;
date | out-file -filepath $LogFile -append -noClobber;
$CleanupScope_expiredUpdates = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$expiredUpdates,$False,$False,$False,$False);
$CleanupManager.PerformCleanup($CleanupScope_expiredUpdates) | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: obsoleteUpdates = " + $obsoleteUpdates | out-file -filepath $LogFile -append -noClobber;
"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;
date | out-file -filepath $LogFile -append -noClobber;
$CleanupScope_obsoleteUpdates = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$False,$obsoleteUpdates,$False,$False,$False);
$CleanupManager.PerformCleanup($CleanupScope_obsoleteUpdates) | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: compressUpdates = " + $compressUpdates | out-file -filepath $LogFile -append -noClobber;
"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;
date | out-file -filepath $LogFile -append -noClobber;
$CleanupScope_compressUpdates = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$False,$False,$compressUpdates,$False,$False);
$CleanupManager.PerformCleanup($CleanupScope_compressUpdates) | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: obsoleteComputers = " + $obsoleteComputers | out-file -filepath $LogFile -append -noClobber;
"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;
date | out-file -filepath $LogFile -append -noClobber;
$CleanupScope_obsoleteComputers = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$False,$False,$False,$obsoleteComputers,$False);
$CleanupManager.PerformCleanup($CleanupScope_obsoleteComputers) | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: unneededContentFiles = " + $unneededContentFiles | out-file -filepath $LogFile -append -noClobber;
"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;
date | out-file -filepath $LogFile -append -noClobber;
$CleanupScope_unneededContentFiles = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$False,$False,$False,$False,$unneededContentFiles);
$CleanupManager.PerformCleanup($CleanupScope_unneededContentFiles) | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

# Log Current Free DiskSpace
$diskafter = ([wmi]$WMIDiskSpaceRequest)
"Free disk space after cleanup:" | out-file -filepath $LogFile -append -noClobber;
"Drive " + $SUSDrive + ": has {0:#.0} GB free of {1:#.0} GB Total" -f ($diskafter.FreeSpace/1GB),($diskafter.Size/1GB) | out-file -filepath $LogFile -append -noClobber;
"" | out-file -filepath $LogFile -append -noClobber;

# Log the date after
$date = date
$info = "End Date / Time: " + $date
$info | out-file -filepath $LogFile -append -noClobber;
#EndRegion SCRIPT

# Source: http://gallery.technet.microsoft.com/scriptcenter/90ca6976-d441-4a10-89b0-30a7103d55db#content
# Mail the report...
$message = new-object Net.Mail.MailMessage
$mailer = new-object Net.Mail.SmtpClient($SMTPServer)
$message.From = $MailFrom #"Sender <Sender@Domain.TLD>"
$message.To.Add($MailTo) #("Recipient <Recipient@Domain.TLD>")
$MeinText = "WSUS - Server CleanUp Report " + $WSUSServer
$message.Subject = $MeinText
$message.Body = [string]::join([environment]::NewLine, (get-content $logfile))
$mailer.Send($message)

#Delete Log => will happen at the next run
#Remove-Item $LogFile

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

# WSUS Connection Parameters:

[String]$WSUSServer = $args[0]

[Boolean]$useSecureConnection = $False

[Int32]$portNumber = $args[12]

[String]$LogFile = $args[1]

[String]$SMTPServer = $args[2]

[String]$MailFrom = $args[3]

[String]$MailTO = $args[4]

[String]$SUSDrive = $args[11]

[String]$WMIDiskSpaceRequest = "\\" + $WSUSServer + "\root\cimv2:Win32_logicalDisk.DeviceID='" + $SUSDrive + ":'"

# Windows PowerShell example to check 'If File Exists'

#$FileExists = Test-Path $LogFile

#If ($FileExists -eq $True) {

#Delete old LogFiles

# Remove-Item $LogFile

# Cleanup Parameters:

# Decline updates that have not been approved for 30 days or more, are not currently needed by any clients, and are superseded by an aproved update.

[Boolean]$supersededUpdates = $True

If ($args[5] = 0) {

$supersededUpdates = $False

}

# Decline updates that aren't approved and have been expired my Microsoft.

[Boolean]$expiredUpdates = $True

If ($args[6] = 0) {

$expiredUpdates = $False

}

# Delete updates that are expired and have not been approved for 30 days or more.

[Boolean]$obsoleteUpdates = $True

If ($args[7] = 0) {

$obsoleteUpdates = $False

}

# Delete older update revisions that have not been approved for 30 days or more.

[Boolean]$compressUpdates = $True

If ($args[8] = 0) {

$compressUpdates = $False

}

# Delete computers that have not contacted the server in 30 days or more.

[Boolean]$obsoleteComputers = $True

If ($args[9] = 0) {

$obsoleteComputers = $False

}

# Delete update files that aren't needed by updates or downstream servers.

[Boolean]$unneededContentFiles = $True

If ($args[10] = 0) {

$unneededContentFiles = $False

}

#EndRegion VARIABLES

#Region SCRIPT

# Load .NET assembly

[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration");

# Connect to WSUS Server

$wsusParent = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($WSUSServer,$useSecureConnection,$portNumber);

# Log the date first

"Microsoft SUS Server CleanUp" | out-file -filepath $LogFile -append -noClobber;

$date = date

$info = "Start Date / Time: " + $date

$info | out-file -filepath $LogFile -append -noClobber;

# Log the SUS Server Name

$info = "Microsoft SUS Server Name: " + $WSUSServer

$info | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

# Log Current Free DiskSpace

$diskbefore = ([wmi]$WMIDiskSpaceRequest)

"Free disk space before cleanup:" | out-file -filepath $LogFile -append -noClobber;

"Drive " + $SUSDrive + ": has {0:#.0} GB free of {1:#.0} GB Total" -f ($diskbefore.FreeSpace/1GB),($diskbefore.Size/1GB) | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

# Perform Cleanup

"Microsoft SUS Server CleanUp Results" | out-file -filepath $LogFile -append -noClobber;

"====================================" | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

$CleanupManager = $wsusParent.GetCleanupManager();

"CleanUp: supersededUpdates = " + $supersededUpdates | out-file -filepath $LogFile -append -noClobber;

"----------------------------------" | out-file -filepath $LogFile -append -noClobber;

date | out-file -filepath $LogFile -append -noClobber;

$CleanupScope_supersededUpdates = New-Object Microsoft.UpdateServices.Administration.CleanupScope($supersededUpdates,$False,$False,$False,$False,$False);

$CleanupManager.PerformCleanup($CleanupScope_supersededUpdates) | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: expiredUpdates = " + $expiredUpdates | out-file -filepath $LogFile -append -noClobber;

"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;

date | out-file -filepath $LogFile -append -noClobber;

$CleanupScope_expiredUpdates = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$expiredUpdates,$False,$False,$False,$False);

$CleanupManager.PerformCleanup($CleanupScope_expiredUpdates) | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: obsoleteUpdates = " + $obsoleteUpdates | out-file -filepath $LogFile -append -noClobber;

"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;

date | out-file -filepath $LogFile -append -noClobber;

$CleanupScope_obsoleteUpdates = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$False,$obsoleteUpdates,$False,$False,$False);

$CleanupManager.PerformCleanup($CleanupScope_obsoleteUpdates) | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: compressUpdates = " + $compressUpdates | out-file -filepath $LogFile -append -noClobber;

"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;

date | out-file -filepath $LogFile -append -noClobber;

$CleanupScope_compressUpdates = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$False,$False,$compressUpdates,$False,$False);

$CleanupManager.PerformCleanup($CleanupScope_compressUpdates) | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: obsoleteComputers = " + $obsoleteComputers | out-file -filepath $LogFile -append -noClobber;

"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;

date | out-file -filepath $LogFile -append -noClobber;

$CleanupScope_obsoleteComputers = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$False,$False,$False,$obsoleteComputers,$False);

$CleanupManager.PerformCleanup($CleanupScope_obsoleteComputers) | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

"CleanUp: unneededContentFiles = " + $unneededContentFiles | out-file -filepath $LogFile -append -noClobber;

"---------------------------------------" | out-file -filepath $LogFile -append -noClobber;

date | out-file -filepath $LogFile -append -noClobber;

$CleanupScope_unneededContentFiles = New-Object Microsoft.UpdateServices.Administration.CleanupScope($False,$False,$False,$False,$False,$unneededContentFiles);

$CleanupManager.PerformCleanup($CleanupScope_unneededContentFiles) | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

# Log Current Free DiskSpace

$diskafter = ([wmi]$WMIDiskSpaceRequest)

"Free disk space after cleanup:" | out-file -filepath $LogFile -append -noClobber;

"Drive " + $SUSDrive + ": has {0:#.0} GB free of {1:#.0} GB Total" -f ($diskafter.FreeSpace/1GB),($diskafter.Size/1GB) | out-file -filepath $LogFile -append -noClobber;

"" | out-file -filepath $LogFile -append -noClobber;

# Log the date after

$date = date

$info = "End Date / Time: " + $date

$info | out-file -filepath $LogFile -append -noClobber;

#EndRegion SCRIPT

# Source: http://gallery.technet.microsoft.com/scriptcenter/90ca6976-d441-4a10-89b0-30a7103d55db#content

# Mail the report...

$message = new-object Net.Mail.MailMessage

$mailer = new-object Net.Mail.SmtpClient($SMTPServer)

$message.From = $MailFrom #"Sender <Sender@Domain.TLD>"

$message.To.Add($MailTo) #("Recipient <Recipient@Domain.TLD>")

$MeinText = "WSUS - Server CleanUp Report " + $WSUSServer

$message.Subject = $MeinText

$message.Body = [string]::join([environment]::NewLine, (get-content $logfile))

$mailer.Send($message)

#Delete Log => will happen at the next run

#Remove-Item $LogFile

Script to remove RemoteApp and Desktop Connections

RADC or RemoteApp and Desktop Connections are very powerful in combination with Windows 7 or newer. You actually can have Terminalserver or RDS / Remote Desktop Server applications in the users start menu and connect to them in seamless window applications.

Windows 7 made it challenging to even implement those applications in a large scale, for this sole purpose you had to use a PowerShell script that actually imported a WCX file. Windows 8 and especially Windows 10 can do this via GPO nowadays.

The GPO settings allow one RDS farm to be added and they of course will remove the RDS farm if the GPO is changed/removed.

But what about those Windows 7 clients that are still out there and those cases where you actually have other RDS / RADC connections that you want to delete, e.g. manually created ones. I just came across this scenario and wanted to share the script I just wrote. I created two files in order to executed it simply via GPO as a Cscsript in order to avoid any dialog boxes coming up.

CSCRIPT "%~dp0\RADC_Delete.vbs"

1	CSCRIPT "%~dp0\RADC_Delete.vbs"

'Define the RDSDNSName - Look up in Registry Key: 
'HKCU\Software\Microsoft\Workspaces\Feeds\{unique number - different on each profile/system}
'Key: WorkspaceId 
'Put the WorkspaceId in to the RDSDNSName variable
Const RDSDNSName = "rds.yourdomain.com"

Dim oReg,fso
Dim strResult,strWorkspaceFolder,strStartMenuRoot
Dim strComputer 
Dim hDefKey

Const HKEY_CURRENT_USER = &H80000001

hDefKey = HKEY_CURRENT_USER 
strComputer = "." 
strKeyPath = "Software\Microsoft\Workspaces\Feeds"
Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
oReg.EnumKey hDefKey, strKeyPath, arrSubKeys
For Each strSubkey In arrSubKeys
strSubKeyPath = strKeyPath & "\" & strSubkey
oReg.EnumValues hDefKey, strSubKeyPath, arrValueNames, arrTypes

On Error Resume Next
oReg.GetStringValue hDefKey, strSubKeyPath, "WorkspaceId", strValue
strResult=cstr(strValue)
oReg.GetStringValue hDefKey, strSubKeyPath, "WorkspaceFolder", strValue
strWorkspaceFolder=cstr(strValue)
oReg.GetStringValue hDefKey, strSubKeyPath, "StartMenuRoot", strValue
strStartMenuRoot=cstr(strValue)
On Error Goto 0

If Len(strResult)>0 Then
If LCase(strResult) = LCase(RDSDNSName) Then
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
If fso.FolderExists(strWorkspaceFolder) Then
fso.DeleteFolder strWorkspaceFolder, True
End If
If fso.FolderExists(strStartMenuRoot) Then
fso.DeleteFolder strStartMenuRoot, True
End If
DeleteSubkeys HKEY_CURRENT_USER, strSubKeyPath 
On Error Goto 0 
Set fso = Nothing
End If
End If
Next
Set oReg = Nothing

Sub DeleteSubkeys(HKEY_CURRENT_USER, strKeyPath) 
oReg.EnumKey HKEY_CURRENT_USER, strKeyPath, arrSubkeys

If IsArray(arrSubkeys) Then 
For Each strSubkey In arrSubkeys 
DeleteSubkeys HKEY_CURRENT_USER, strKeyPath & "\" & strSubkey 
Next 
End If

oReg.DeleteKey HKEY_CURRENT_USER, strKeyPath 
End Sub

'Define the RDSDNSName - Look up in Registry Key:

'HKCU\Software\Microsoft\Workspaces\Feeds\{unique number - different on each profile/system}

'Key: WorkspaceId

'Put the WorkspaceId in to the RDSDNSName variable

Const RDSDNSName = "rds.yourdomain.com"

Dim oReg,fso

Dim strResult,strWorkspaceFolder,strStartMenuRoot

Dim strComputer

Dim hDefKey

Const HKEY_CURRENT_USER = &H80000001

hDefKey = HKEY_CURRENT_USER

strComputer = "."

strKeyPath = "Software\Microsoft\Workspaces\Feeds"

Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")

oReg.EnumKey hDefKey, strKeyPath, arrSubKeys

For Each strSubkey In arrSubKeys

strSubKeyPath = strKeyPath & "\" & strSubkey

oReg.EnumValues hDefKey, strSubKeyPath, arrValueNames, arrTypes

On Error Resume Next

oReg.GetStringValue hDefKey, strSubKeyPath, "WorkspaceId", strValue

strResult=cstr(strValue)

oReg.GetStringValue hDefKey, strSubKeyPath, "WorkspaceFolder", strValue

strWorkspaceFolder=cstr(strValue)

oReg.GetStringValue hDefKey, strSubKeyPath, "StartMenuRoot", strValue

strStartMenuRoot=cstr(strValue)

On Error Goto 0

If Len(strResult)>0 Then

If LCase(strResult) = LCase(RDSDNSName) Then

Set fso = CreateObject("Scripting.FileSystemObject")

On Error Resume Next

If fso.FolderExists(strWorkspaceFolder) Then

fso.DeleteFolder strWorkspaceFolder, True

End If

If fso.FolderExists(strStartMenuRoot) Then

fso.DeleteFolder strStartMenuRoot, True

End If

DeleteSubkeys HKEY_CURRENT_USER, strSubKeyPath

On Error Goto 0

Set fso = Nothing

End If

Set oReg = Nothing

Sub DeleteSubkeys(HKEY_CURRENT_USER, strKeyPath)

oReg.EnumKey HKEY_CURRENT_USER, strKeyPath, arrSubkeys

If IsArray(arrSubkeys) Then

For Each strSubkey In arrSubkeys

DeleteSubkeys HKEY_CURRENT_USER, strKeyPath & "\" & strSubkey

End If

oReg.DeleteKey HKEY_CURRENT_USER, strKeyPath

End Sub

The .CMD executes the .VBS an expects it in the same directory of course. In the .VBS you will need to change the 5th line – as inidicated. Everything else you can leave as is. Of course this script will only delete the specified connection. You could define the line 5 parameter and change line 33 from

If LCase(strResult) = LCase(RDSDNSName) Then

1	If LCase(strResult) = LCase(RDSDNSName) Then

to the following line

If LCase(strResult) = LCase(RDSDNSName) Then

1	If LCase(strResult) = LCase(RDSDNSName) Then

This would result in to deleted everything but the defined connection and therefor do a cleanup. In theory you could then put a empty string in line 5 and just clean up everything.

As always, I hope some of you find this helpful.

June 20, 2018 by Florian Rossmark configuration scripts solutions windows

Script based SQL Express backups

SQL Express is widely used but has huge downside, there is no SQL Agent available. Even Windows internal databases, especially WSUS / Windows Update Services / Microsoft Update Services are in the end SQL Express like databases that do not have a SQL Agent.

Now, you can have central SQL Servers with Agents have them backed up – and I recommend on doing so if possible. But for the many times this is not possible, you will need to find another way to create those nice little .BAK files for SQL internal backups aka. SQL Maintenance Plan Backups. To work around this issue, I once wrote a script that automates this for each database found on a specific SQL server. It creates the backups via SQLCMD commands and even does a clean up of obsolete files (files older than x days), almost like SQL Maintenance Plans do it.

The script is divided in to a .CMD file that executes the actual backup and where you set the configuration/parameters and a .VBS file that is controlled by the actual .CMD script and will perform the backup cleanup. In the end you can have the .CMD send a email report – I used the SMTPSEND program from Michael Kocum (https://www.dataenter.com/download.asp) for this since I already ad it flying around – you could replace the mail send option with another prepared SMTPSEND client, a VBS script or just remove it completely.

Adjusting the settings / parameter:

This is all done in the SQLBACKUP.CMD file – the header section pretty much will explain all you need to know, from SQL Server to SQL-User and Password over Mail-Server to recipients.

If you want to execute the SQLBackups as the Windows-User that is executing the script, you need to exchange the REM (remarks) for the following two lines further down in the scripts. I apologize for the inconvenience, this is a old script I never updated to have those settings in the header (more automated), I always just changed the lines.

REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication
SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" &gt; "%DBList%"
REM the following line uses Windows User authentication - remove the REM if you want to use this instead
REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" &gt; "%DBList%"

REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication

SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

REM the following line uses Windows User authentication - remove the REM if you want to use this instead

REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

Everything else should be rather easy. Of course you will need sufficient access rights to the SQL databases and your destination backup folder. The task-scheduler might work best if you execute the script with “cmd /c c:\scripts\sqlbackup.cmd” (change the path as you need it) and set the working directory / startup folder right. It might help to execute the task with elevated rights etc. – all depending on your systems configuration.

Below are the two scripts – I hope this helps some of you. The generated .BAK files can simply be restored in SQL services via the GUI cause they are native SQL backup files.

@ECHO OFF
SETLOCAL

REM Variables - please adjust
REM =============================================================================================
REM Server or Server\Instance
SET SQLServer=SERVER\INSTANANCEifNEEDED
REM local Path or UNC Path (must be accessible under the executing user!)
SET SQLBackupPath=D:\Backup\SubFolderName----\\SERVER\SHARE\FOLDER-----EitherLocalOrUNC
REM max age the files can be in days before they get deleted
SET SQLBackupMaxAge=1 
SET SQLUser=sa
SET SQLPW=
SET MailServer=relay.domain.local
SET MailServerPort=25
SET MailFrom=sqlbackup@domain.com
SET MailTo=backups@domain.com
SET MailSubject=SQL Express Backup (INSTANCENAME/DESCRIPTION)
REM =============================================================================================

REM Script-System-Variables - do not change after this point!
SET ScriptLog=%~dp0\SQLBackup_Script_Log.txt
REM Running Script 
DEL %ScriptLog% 
ECHO Script-Log Date: %Date% / Time: %Time% >> %ScriptLog%
ECHO Script-Parameter: >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%
ECHO SQLServer: %SQLServer% >> %ScriptLog%
ECHO SQLBackupPath: %SQLBackupPath% >> %ScriptLog%
ECHO SQLBackupMaxAge: %SQLBackupMaxAge% >> %ScriptLog%
ECHO SQLUser: %SQLUser% >> %ScriptLog%
ECHO SQLPW: %SQLPW% >> %ScriptLog%
ECHO MailServer: %MailServer% >> %ScriptLog%
ECHO MailServerPort: %MailServerPort% >> %ScriptLog%
ECHO MailFrom: %MailFrom% >> %ScriptLog%
ECHO MailTo: %MailTo% >> %ScriptLog%
ECHO MailSubject: %MailSubject% >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%
ECHO Script executing: >> %ScriptLog%

ECHO Delete old Backup Info File >> %ScriptLog%
DEL %SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ECHO Get date in format YYYY-MM-DD (assumes the locale is the United States) >> %ScriptLog%
FOR /F "tokens=1,2,3,4 delims=. " %%A IN ('Date /T') DO SET NowDate=%%C-%%B-%%A >> %ScriptLog%
FOR /F "tokens=1,2 delims=: " %%A IN ('Time /T') DO SET NowDate=%NowDate%_%%A-%%B >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ECHO Build a list of databases to backup >> %ScriptLog%
SET DBList=%SQLBackupPath%\SQLDBList.txt
REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication
SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"
REM the following line uses Windows User authentication - remove the REM if you want to use this instead
REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"
ECHO =========================================================================== >> %ScriptLog%

ECHO Backup each database, prepending the date to the filename >> %ScriptLog%
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I >> %ScriptLog%
ECHO Backing up database: %%I >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO Destination: %SQLBackupPath%\%NowDate%_%%I.bak >>%SQLBackupPath%\SQLBackupStatus.txt
SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -Q "BACKUP DATABASE [%%I] TO Disk='%SQLBackupPath%\ %NowDate%_%%I.bak'" >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt
)
ECHO =========================================================================== >> %ScriptLog%
ECHO Clean up the temp file >> %ScriptLog%
IF EXIST "%DBList%" DEL /F /Q "%DBList%" >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ECHO Old Backups Clean Up >> %ScriptLog%
CSCRIPT %~dp0\SQLBackup.vbs %SQLBackupMaxAge% "%SQLBackupPath%" >> %ScriptLog%
ECHO SQL Backup CleanUp executed >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO Currently available Backup-Files: >>%SQLBackupPath%\SQLBackupStatus.txt
DIR %SQLBackupPath% >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO =========================================================================== >> %ScriptLog%

ECHO Send Info Mail >> %ScriptLog%
%~dp0\SMTPSEND -f%MailFrom% -t%MailTo% -h%MailServer% -p%MailServerPort% -s%MailSubject% -i%SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ENDLOCAL

@ECHO OFF

SETLOCAL

REM Variables - please adjust

REM =============================================================================================

REM Server or Server\Instance

SET SQLServer=SERVER\INSTANANCEifNEEDED

REM local Path or UNC Path (must be accessible under the executing user!)

SET SQLBackupPath=D:\Backup\SubFolderName----\\SERVER\SHARE\FOLDER-----EitherLocalOrUNC

REM max age the files can be in days before they get deleted

SET SQLBackupMaxAge=1

SET SQLUser=sa

SET SQLPW=

SET MailServer=relay.domain.local

SET MailServerPort=25

SET MailFrom=sqlbackup@domain.com

SET MailTo=backups@domain.com

SET MailSubject=SQL Express Backup (INSTANCENAME/DESCRIPTION)

REM =============================================================================================

REM Script-System-Variables - do not change after this point!

SET ScriptLog=%~dp0\SQLBackup_Script_Log.txt

REM Running Script

DEL %ScriptLog%

ECHO Script-Log Date: %Date% / Time: %Time% >> %ScriptLog%

ECHO Script-Parameter: >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO SQLServer: %SQLServer% >> %ScriptLog%

ECHO SQLBackupPath: %SQLBackupPath% >> %ScriptLog%

ECHO SQLBackupMaxAge: %SQLBackupMaxAge% >> %ScriptLog%

ECHO SQLUser: %SQLUser% >> %ScriptLog%

ECHO SQLPW: %SQLPW% >> %ScriptLog%

ECHO MailServer: %MailServer% >> %ScriptLog%

ECHO MailServerPort: %MailServerPort% >> %ScriptLog%

ECHO MailFrom: %MailFrom% >> %ScriptLog%

ECHO MailTo: %MailTo% >> %ScriptLog%

ECHO MailSubject: %MailSubject% >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO Script executing: >> %ScriptLog%

ECHO Delete old Backup Info File >> %ScriptLog%

DEL %SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO Get date in format YYYY-MM-DD (assumes the locale is the United States) >> %ScriptLog%

FOR /F "tokens=1,2,3,4 delims=. " %%A IN ('Date /T') DO SET NowDate=%%C-%%B-%%A >> %ScriptLog%

FOR /F "tokens=1,2 delims=: " %%A IN ('Time /T') DO SET NowDate=%NowDate%_%%A-%%B >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO Build a list of databases to backup >> %ScriptLog%

SET DBList=%SQLBackupPath%\SQLDBList.txt

REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication

SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

REM the following line uses Windows User authentication - remove the REM if you want to use this instead

REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

ECHO =========================================================================== >> %ScriptLog%

ECHO Backup each database, prepending the date to the filename >> %ScriptLog%

FOR /F "tokens=*" %%I IN (%DBList%) DO (

ECHO Backing up database: %%I >> %ScriptLog%

ECHO Backing up database: %%I >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO Destination: %SQLBackupPath%\%NowDate%_%%I.bak >>%SQLBackupPath%\SQLBackupStatus.txt

SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -Q "BACKUP DATABASE [%%I] TO Disk='%SQLBackupPath%\ %NowDate%_%%I.bak'" >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt

)

ECHO =========================================================================== >> %ScriptLog%

ECHO Clean up the temp file >> %ScriptLog%

IF EXIST "%DBList%" DEL /F /Q "%DBList%" >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO Old Backups Clean Up >> %ScriptLog%

CSCRIPT %~dp0\SQLBackup.vbs %SQLBackupMaxAge% "%SQLBackupPath%" >> %ScriptLog%

ECHO SQL Backup CleanUp executed >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO Currently available Backup-Files: >>%SQLBackupPath%\SQLBackupStatus.txt

DIR %SQLBackupPath% >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO =========================================================================== >> %ScriptLog%

ECHO Send Info Mail >> %ScriptLog%

%~dp0\SMTPSEND -f%MailFrom% -t%MailTo% -h%MailServer% -p%MailServerPort% -s%MailSubject% -i%SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ENDLOCAL

Set objArgs = WScript.Arguments

If objArgs.Count > 0 Then

Dim strPath
Dim intAge

intAge = objArgs(0)
strPath = objArgs(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile in objFolder.Files
If DateDiff("d", objFile.DateLastModified, Now()) > Cint(intAge) Then
On Error Resume Next
objFile.Delete
On Error Goto 0
End If
Next

End If

Set objArgs = WScript.Arguments

If objArgs.Count > 0 Then

Dim strPath

Dim intAge

intAge = objArgs(0)

strPath = objArgs(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(strPath)

For Each objFile in objFolder.Files

If DateDiff("d", objFile.DateLastModified, Now()) > Cint(intAge) Then

On Error Resume Next

objFile.Delete

On Error Goto 0

End If

June 19, 2018 by Florian Rossmark recommendations scripts server

SQL Database backup monitoring

The following article was originally posted here by myself: https://kb.paessler.com/en/topic/79665-sql-database-backup-monitoring

SQL backups and their monitoring is one of the most important things. We often talk about rather complex situations including transaction-logfiles and various other stuff.

Monitoring those things was something that did cost us to many sensors with the standard-scripts etc. and was not really effective.

In order to change this – here are two scripts that will be able to solve most of your issues – you find them both at the end of this posting:

SQLBackupDestinationCheck.vbs
SQL_Database_Full_Backups.sql

SQLBackupDestinationCheck.vbs: This is a VBS script that will return XML content to Paessler/PRTG in multiple channels while using one sensor.

It expects three parameters:

go through first level sub folders: 0 (no) or 1 (yes)
file extension to obey – any other extension will be ignored – in most cases: “bak”
Path – should mostly be an UNC path

It will return those channels:

Total file count: count of all files with this extension in all folders checked
Total folder count: count of all folders checked
Oldest file found in days: oldest file – value gives back age in days
Newest file found in days: newest file – value gives back age in days
Lowest files in folder count found: lowest count of files that have been found in one folder
Highest files in folder count found: max. files that have been found in one folder

This needs some explanation:
The script checks a path for files with a certain extension. Let’s say you do SQL maintenance plans and use the extension .BAK to write those. You do a daily backup and keep them for 3 days to make sure they end up on a tape, further do you use sub-folders per database and you have a total of 5 databases on this system – now you will need to configure error-limits per channel – e.g.:

5 databases x 3 days = 15 files in total - 3 files per folder are expected, 5 folders are expected

1	5 databases x 3 days = 15 files in total - 3 files per folder are expected, 5 folders are expected

Total file count: lower limit: 10 files – upper limit: 20 files – during the backup you might have up to 20 files
Total folder count: 5 folders upper and lower limit – more/less then 5 would mean something changed
Oldest file found in days: lower limit 2 days – upper limit 4 days – older then 4 would mean the cleanup does not work
Newest file found in days: lower limit 0 days – upper limit 2 days – nothing newer (date issues? and nothing older as well)
Lowest files in folder count found: lower limit: 2 – there should be always more then 2x .BAK files in any subfolder
Highest files in folder count found: upper limit: 4 – anything above again would mean some clean up is not working right for one database

So – keep in mind – you can get more fancy with WARNING limits and ERROR limits – the example above will help you understand what to do – this should help you getting started. The script will save you quite a few sensors and still keep a pretty close watch on the file-system side of SQL backups – of course you could abuse it for something else then SQL backups as well – but this was my main intent for this script.
SQL_Database_Full_Backups.sql
This file will request information about backups for SQL itself. It might need a SQL 2005 or newer to work – and yes – I did post this on another PRTG KB thread – but I wanted to have the complete solution in this one post.

The script will be executed against the SQL server instance the databases reside, on the master-database. You need to specify a parameter that will be set as “@MaxHours” – this actually should be something like 26 hours, so your SQL backups are never exciting 26 hours – giving the backup some time to run as well. More for bigger databases might be necessary. If you do multiple full-backups per day, set it to e.g. 2 hours or what ever your limit is.

You will get backup 3x columns:

TotalAmountOfDatabases	Total amount of databases of this server – this allows you not only to watch if anyone created/deleted a database on the server, it also gives you a good base-line in general
RecentlyBackupUpCoun	How many databases have been backed up recently – full backup – in specified time-window
NOTRecentlyBackupUpCount	How many have not been backed up in the same time window

RecentlyBackupUpCount and NOTRecentlyBackupUpCount should always match up to TotalAmountOfDatabases – but that’s not the point. More important is – you might have backed up databases and not backed up databases – set you error-limits for all three columns accordingly – upper and lower limit – and you will see that the alert will fire if you add a database or keep the SQL agent service stopped so it hops over a single backup and misses it…

Folder: C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML
File: SQLBackupDestinationCheck.vbs

'Execute:
'cscript SQLBackupDestinationCheck.vbs <0 or 1> <"bak"> <"path">
'cscript SQLBackupDestinationCheck.vbs 1 "bak" "\\SERVER.DOMAIN.LOCAL\SQLBackups\SubFolder"

Dim bSubFolders
Dim sExtension
Dim sPath

If WScript.Arguments.Count = 3 Then
	bSubFolders = WScript.Arguments.Item(0)
	sExtension= WScript.Arguments.Item(1)
	sPath = WScript.Arguments.Item(2)
Else
	Wscript.Echo "Usage: cscript SQLBackupDestinationCheck.vbs 1 ""bak"" ""\\SERVER.DOMAIN.LOCAL\SQLBackups\SubFolder"""
	Wscript.Quit
End If

Dim fs
Dim objFolder
Dim objSubFolder
Dim objFile

Dim iReturnFileCount
Dim iReturnFolderCount
Dim iReturnOldestFile
Dim iReturnYoungestFile
Dim iReturnMinFilesPerFolder
Dim iReturnMaxFilesPerFolder

iReturnFileCount = -1
iReturnFolderCount = -1
iReturnOldestFile = -1
iReturnYoungestFile = 9999
iReturnMinFilesPerFolder = 9999
iReturnMaxFilesPerFolder = -1

Dim iFileDateDiff 
Dim iFileCount

Set fs = CreateObject("Scripting.FileSystemObject")
	If fs.FolderExists(sPath) Then
		Set objFolder = fs.GetFolder(sPath)
			If bSubFolders = 1 Then
				iReturnFileCount = 0
				iReturnFolderCount = objFolder.SubFolders.Count
				For Each objSubFolder In objFolder.SubFolders
					iFileCount = 0
					For Each objFile in objSubFolder.Files
						If LCase(Right(objFile.Name,(1+Len(sExtension)))) = LCase("." & sExtension) Then
							iFileCount = iFileCount + 1
							iFileDateDiff = Datediff("d",objFile.DateLastModified,Now)
							If iFileDateDiff < iReturnYoungestFile Then iReturnYoungestFile = iFileDateDiff
							If iFileDateDiff > iReturnOldestFile Then iReturnOldestFile = iFileDateDiff							
						End If						
					Next
					iReturnFileCount = iReturnFileCount + iFileCount
					If iFileCount < iReturnMinFilesPerFolder Then iReturnMinFilesPerFolder = iFileCount
					If iFileCount > iReturnMaxFilesPerFolder Then iReturnMaxFilesPerFolder = iFileCount						
				Next
			Else
				iReturnFolderCount = 1
				iFileCount = 0
				For Each objFile in objFolder.Files
					If LCase(Right(objFile.Name,(1+Len(sExtension)))) = LCase("." & sExtension) Then
						iFileCount = iFileCount + 1
						iFileDateDiff = Datediff("d",objFile.DateLastModified,Now)
						If iFileDateDiff < iReturnYoungestFile Then iReturnYoungestFile = iFileDateDiff
						If iFileDateDiff > iReturnOldestFile Then iReturnOldestFile = iFileDateDiff							
					End If						
				Next
				iReturnFileCount = iFileCount
				If iFileCount < iReturnMinFilesPerFolder Then iReturnMinFilesPerFolder = iFileCount
				If iFileCount > iReturnMaxFilesPerFolder Then iReturnMaxFilesPerFolder = iFileCount						
			End If
		Set objFolder = Nothing
	End if
Set fs = Nothing

WScript.echo "<prtg>"
WScript.echo "<result>"
WScript.echo "<channel>Total file count</channel><value>" & iReturnFileCount & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Total folder count</channel><value>" & iReturnFolderCount & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Oldest file found in days</channel><value>" & iReturnOldestFile & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Newest file found in days</channel><value>" & iReturnYoungestFile & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Lowest files in folder count found</channel><value>" & iReturnMinFilesPerFolder & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Highest files in folder count found</channel><value>" & iReturnMaxFilesPerFolder & "</value>"
WScript.echo "</result>"
WScript.echo "</prtg>"

'Execute:

'cscript SQLBackupDestinationCheck.vbs <0 or 1> <"bak"> <"path">

'cscript SQLBackupDestinationCheck.vbs 1 "bak" "\\SERVER.DOMAIN.LOCAL\SQLBackups\SubFolder"

Dim bSubFolders

Dim sExtension

Dim sPath

If WScript.Arguments.Count = 3 Then

bSubFolders = WScript.Arguments.Item(0)

sExtension= WScript.Arguments.Item(1)

sPath = WScript.Arguments.Item(2)

Else

Wscript.Echo "Usage: cscript SQLBackupDestinationCheck.vbs 1 ""bak"" ""\\SERVER.DOMAIN.LOCAL\SQLBackups\SubFolder"""

Wscript.Quit

End If

Dim fs

Dim objFolder

Dim objSubFolder

Dim objFile

Dim iReturnFileCount

Dim iReturnFolderCount

Dim iReturnOldestFile

Dim iReturnYoungestFile

Dim iReturnMinFilesPerFolder

Dim iReturnMaxFilesPerFolder

iReturnFileCount = -1

iReturnFolderCount = -1

iReturnOldestFile = -1

iReturnYoungestFile = 9999

iReturnMinFilesPerFolder = 9999

iReturnMaxFilesPerFolder = -1

Dim iFileDateDiff

Dim iFileCount

Set fs = CreateObject("Scripting.FileSystemObject")

If fs.FolderExists(sPath) Then

Set objFolder = fs.GetFolder(sPath)

If bSubFolders = 1 Then

iReturnFileCount = 0

iReturnFolderCount = objFolder.SubFolders.Count

For Each objSubFolder In objFolder.SubFolders

iFileCount = 0

For Each objFile in objSubFolder.Files

If LCase(Right(objFile.Name,(1+Len(sExtension)))) = LCase("." & sExtension) Then

iFileCount = iFileCount + 1

iFileDateDiff = Datediff("d",objFile.DateLastModified,Now)

If iFileDateDiff < iReturnYoungestFile Then iReturnYoungestFile = iFileDateDiff

If iFileDateDiff > iReturnOldestFile Then iReturnOldestFile = iFileDateDiff

End If

iReturnFileCount = iReturnFileCount + iFileCount

If iFileCount < iReturnMinFilesPerFolder Then iReturnMinFilesPerFolder = iFileCount

If iFileCount > iReturnMaxFilesPerFolder Then iReturnMaxFilesPerFolder = iFileCount

Else

iReturnFolderCount = 1

iFileCount = 0

For Each objFile in objFolder.Files

If LCase(Right(objFile.Name,(1+Len(sExtension)))) = LCase("." & sExtension) Then

iFileCount = iFileCount + 1

iFileDateDiff = Datediff("d",objFile.DateLastModified,Now)

If iFileDateDiff < iReturnYoungestFile Then iReturnYoungestFile = iFileDateDiff

If iFileDateDiff > iReturnOldestFile Then iReturnOldestFile = iFileDateDiff

End If

iReturnFileCount = iFileCount

If iFileCount < iReturnMinFilesPerFolder Then iReturnMinFilesPerFolder = iFileCount

If iFileCount > iReturnMaxFilesPerFolder Then iReturnMaxFilesPerFolder = iFileCount

End If

Set objFolder = Nothing

End if

Set fs = Nothing

WScript.echo "<prtg>"

WScript.echo "<result>"

WScript.echo "<channel>Total file count</channel><value>" & iReturnFileCount & "</value>"

WScript.echo "</result>"

WScript.echo "<result>"

WScript.echo "<channel>Total folder count</channel><value>" & iReturnFolderCount & "</value>"

WScript.echo "</result>"

WScript.echo "<result>"

WScript.echo "<channel>Oldest file found in days</channel><value>" & iReturnOldestFile & "</value>"

WScript.echo "</result>"

WScript.echo "<result>"

WScript.echo "<channel>Newest file found in days</channel><value>" & iReturnYoungestFile & "</value>"

WScript.echo "</result>"

WScript.echo "<result>"

WScript.echo "<channel>Lowest files in folder count found</channel><value>" & iReturnMinFilesPerFolder & "</value>"

WScript.echo "</result>"

WScript.echo "<result>"

WScript.echo "<channel>Highest files in folder count found</channel><value>" & iReturnMaxFilesPerFolder & "</value>"

WScript.echo "</result>"

WScript.echo "</prtg>"

Folder: C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mssql
File: SQL_Database_Full_Backups.sql

-- custom script by Florian Rossmark 
USE master
DECLARE @MaxHours int
SET @MaxHours = @prtg

SELECT 
-- how many databases do we have on this server?
(
	SELECT COUNT(Name) FROM sys.sysdatabases WHERE name NOT LIKE 'tempdb'
) AS TotalAmountOfDatabases,

(
	-- how many databases where backed up within the last n hours?
	SELECT COUNT(BackedUpDBs.DatabaseName) FROM  (
		SELECT sdb.Name AS DatabaseName
		,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate
		,MAX(bus.backup_finish_date) AS LastBackupDateTime
		,(MAX(DATEDIFF(hh, GetDate(), bus.backup_finish_date))*-1) AS HoursAgo

		FROM sys.sysdatabases sdb
			LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

		WHERE 1=1 -- 1=1 as dummy placeholder
		AND sdb.Name NOT LIKE 'tempdb' --we ignore tempdb
		AND bus.type = 'D' -- only obey DATABASE backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
		AND bus.is_damaged = 0
		--AND bus.recovery_model = 'FULL' -- only obey FULL backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx 
		--no checking recovery model, this can be set in the settings per DB
		AND bus.backup_finish_date IS NOT NULL -- since this can be null, we only check for those which aren't

		GROUP BY sdb.Name 
	) AS BackedUpDBs 
	WHERE HoursAgo <= @MaxHours
) AS RecentlyBackupUpCount,

(
	-- how many databases where NOT backed up within the last n hours?
	SELECT COUNT(NotBackedUpDBs.DatabaseName) FROM  (
	--SELECT NotBackedUpDBs.DatabaseName, * FROM  (
		SELECT sdb.Name AS DatabaseName
		,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate
		,MAX(bus.backup_finish_date) AS LastBackupDateTime
		,(MAX(DATEDIFF(hh, GetDate(), bus.backup_finish_date))*-1) AS HoursAgo

		FROM sys.sysdatabases sdb
		LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

		WHERE 1=1 -- 1=1 as dummy placeholder
		AND sdb.Name NOT LIKE 'tempdb' --we ignore tempdb
		AND bus.type = 'D' -- only obey DATABASE backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
		--AND bus.recovery_model = 'FULL' -- only obey FULL backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx 
		--no checking recovery model, this can be set in the settings per DB

		GROUP BY sdb.Name 
	) AS NotBackedUpDBs
	WHERE HoursAgo > @MaxHours
) AS NOTRecentlyBackupUpCount

-- custom script by Florian Rossmark

USE master

DECLARE @MaxHours int

SET @MaxHours = @prtg

SELECT

-- how many databases do we have on this server?

(

SELECT COUNT(Name) FROM sys.sysdatabases WHERE name NOT LIKE 'tempdb'

) AS TotalAmountOfDatabases,

(

-- how many databases where backed up within the last n hours?

SELECT COUNT(BackedUpDBs.DatabaseName) FROM (

SELECT sdb.Name AS DatabaseName

,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate

,MAX(bus.backup_finish_date) AS LastBackupDateTime

,(MAX(DATEDIFF(hh, GetDate(), bus.backup_finish_date))*-1) AS HoursAgo

FROM sys.sysdatabases sdb

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

WHERE 1=1 -- 1=1 as dummy placeholder

AND sdb.Name NOT LIKE 'tempdb' --we ignore tempdb

AND bus.type = 'D' -- only obey DATABASE backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx

AND bus.is_damaged = 0

--AND bus.recovery_model = 'FULL' -- only obey FULL backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx

--no checking recovery model, this can be set in the settings per DB

AND bus.backup_finish_date IS NOT NULL -- since this can be null, we only check for those which aren't

GROUP BY sdb.Name

) AS BackedUpDBs

WHERE HoursAgo <= @MaxHours

) AS RecentlyBackupUpCount,

(

-- how many databases where NOT backed up within the last n hours?

SELECT COUNT(NotBackedUpDBs.DatabaseName) FROM (

--SELECT NotBackedUpDBs.DatabaseName, * FROM (

SELECT sdb.Name AS DatabaseName

,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate

,MAX(bus.backup_finish_date) AS LastBackupDateTime

,(MAX(DATEDIFF(hh, GetDate(), bus.backup_finish_date))*-1) AS HoursAgo

FROM sys.sysdatabases sdb

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

WHERE 1=1 -- 1=1 as dummy placeholder

AND sdb.Name NOT LIKE 'tempdb' --we ignore tempdb

AND bus.type = 'D' -- only obey DATABASE backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx

--AND bus.recovery_model = 'FULL' -- only obey FULL backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx

--no checking recovery model, this can be set in the settings per DB

GROUP BY sdb.Name

) AS NotBackedUpDBs

WHERE HoursAgo > @MaxHours

) AS NOTRecentlyBackupUpCount

June 1, 2018 by Florian Rossmark monitoring prtg recommendations scripts

script

Automate your SUS clean up

Script to remove RemoteApp and Desktop Connections

Script based SQL Express backups

SQL Database backup monitoring

Recent blog posts

Blog Archives

script

Recent blog posts

Blog Archives

Tags