database

Auditing network users against HR lists etc.

Auditing network users against HR lists – a topic that is often overlooked or causes some headache due to e.g. name variations, while it is so important to make sure that your Active Directory is a clean and up to date as possible.

There are big paid solutions out there, but unless you have the budget, resources and processes in place, you will need a simpler approach. Having worked in various sized businesses, let me make some suggestions here. Keep in mind, not all of it might be applicable or best for you, but I hope it will at least provide you some ideas and help to improve your network security.

Structured groups and rights

Before we begin – you should always make any effort to have a very well structured rights base. Avoid cross use of e.g. groups for mail distribution and NTFS file system access. It seems like a good idea until someone needs access to the NTFS path and boom he/she receives the group based communication as well. This is of course just one example. Structure your file systems and right assignments well. All of it can make all the difference. Don’t complicate things, keep it simple.

Monitoring Active Directory activity

What you want is something that constantly looks for any changes to Active Directory, at a bare minimum new users and deleted users as well as group-membership changes. There is some software out there to do this, some is free, often with limited functionality, some you need to buy. Personally I was working on a Windows Service to monitor Active Directory changes, but my time is limited and to this day I did not finish it. Having said this, the IT-Asset Management Database on this website actually has a module that does just this, it monitors the most important activities while it does actually a compare of gathered SQL data against current Active Directory information and eventually sends you a daily report about changes.

Such reports might not be perfect, as they don’t real-time monitor such activity, rather then only send you daily summary reports. Paessler PRTG in combination with either some default sensors or custom scripts like Group Membership change and password reset monitoring or the more specific script for group-membership monitoring are more then helpful. Monitor especially e.g. Domain Admins groups and other groups that would allow access to sensitive areas and data of your network. Such active alerts due to a network monitoring solutions might give you the chance to act fast.

Auditing your user base against HR (Human Resources) data

Again, there is software and solutions out there that can do this automatically or help you with your efforts. But often HR is simply using their Payroll platforms and depending on what they have, it won’t have the functionality you need or they are reluctant to implement such processes or possibly provide you access after all.

The main issue I came across is that there is a difference between the HR data and what the full name in your Active Directory (e.g.) is. You can’t further not just automate user name prediction based on any HR export data you have, cause there likely will be duplicate names as well, depending on how you create user names.

HR normally has an employee number, they should be able to provide this in any employee export to you. Now, Active Directory has actually some attributes that you easily can engage: EmployeeID, employeeNumber and employeeType. PowerShell is your friend, if you want to set them. I highly recommend to use PowerShell to some extend if you create new users. Look at the CheckLists in combination with employees in the IT-Asset Management Database as well for some hints and automation. Microsoft’s Set-ADUser PowerShell command will be helpful as well.

Eventually use a tool like the IT-Admins tool to read your users from your Active Directory and export to Excel. Once you have this, you compare the HR list against the Active Directory export. Don’t bother with VLOOKUP – research the use of INDEX/MATCH in Excel. Format both tables as tables in Excel and document your process, as this might depend a bit on what tools you engaged and how the eventual data looks like. You should end up comparing the HR employee number against the HR employee number stored and exported from Active Directory. This should give you a quick and clean overview. What you should be on the lookout for are those N/A error in Excel in the compare column, as well as possibly HR data that indicates a termination or change. You can go as far as compare the department information, again there are Active Directory attributes for this as well. Department names and department IDs.

If you want to go another step, start comparing group-membership as well. Export all the groups and members, again the IT-Admins Tool can help you here. View it possibly from both sides, the group and members view as well as user is member of groups view. Have the department owners take a look at it as well, they might want to see this.

And step three will be an NTFS rights review. Never ever should there be a user account directly used to assign rights in NTFS. This always should be done via groups. How ever, to review this I again recommend using the IT-Admins Tool, as this is actually designed to help you with the process and is able to export the needed data rather quick and simple.

Don’t forget your ERP systems and systems with Active Directory independent user bases

It is not always possible to rely on Active Directory as only source for your user base. Even if you can, the right assignment in e.g. your ERP system to functions likely is independent from Active Directory. Look in to any possibility of your ERP, either API’s or possibly dig in to the database (or where ever the data is stored), to find out about right changes (groups) and review those lists as well against HR information periodically.

Office 365

Oh – it is synchronized with Active Directory, right? Well – review it anyways. There might be a user object that is not synchronized and exists only in Office 365, groups as well. Review the rights to access certain administrative areas within Office 365.

You should definitive review third party users – especially Microsoft TEAMS and sharing e.g. SharePoint or OneDrive files and folders with outside of the organization will likely create some guest accounts. Keep an eye on those.

And then the third party applications – you need to keep an eye on those, as they can cause possible harm or gain access to sensible data. Users/Employees often will install them without reviewing them thoroughly, or they simply don’t realize they might have been there and share confidential data.

Account breach / compromise and API keys in Office 365 should also be something you want to keep a good eye on. Clicking on the wrong link (it will happen!), entering the password and it is to late. Don’t think a simple password reset will solve the issue. Don’t only rely on your MFA. Review does API keys especially in Office 365. What can happen is that the password is used right away to install an API based access to Office 365 that will then independently from password changes have access to the data. Keep an eye on those things as well!

Summarize SQL server database file size information as a single row

The SQL script below will show you the summarized database file size and database log file sizes on the current database. This can be helpful to monitor the actual file sizes in a single row / line.

This script was also posted in the Paessler PRTG KB, where the initial request for this came up. Posting it here cause I think it can become useful for some. The initial code used and modified is from Tri Effendi SS of stack exchange.

You will need to adjust the USE statement in the first line to the database you want to gather the information from.

USE [master]

SELECT 
	MYROWS.DATABASENAME, 
	SUM(MYROWS.FILESIZE) AS DATA_FILESIZE, SUM(MYROWS.USEDSPACE) AS DATA_USEDSPACE, SUM(MYROWS.FREESPACE) AS DATA_FREESPACE, SUM(MYROWS.[FREESPACE_%]) AS DATA_FREESPACE_PERCENT,
	SUM(MYLOGS.FILESIZE) AS LOG_FILESIZE, SUM(MYLOGS.USEDSPACE) AS LOG_USEDSPACE, SUM(MYLOGS.FREESPACE) AS LOG_FREESPACE, SUM(MYLOGS.[FREESPACE_%]) AS LOG_FREESPACE_PERCENT 
FROM
	(
		SELECT 
			DB_NAME() AS DATABASENAME
			,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024
			,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024
			,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024
			,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
		FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
		WHERE A.type_desc LIKE 'ROWS'
	) MYROWS 
	LEFT JOIN 
	(
		SELECT 
			DB_NAME() AS DATABASENAME
			,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024
			,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024
			,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024
			,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
		FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
		WHERE A.type_desc LIKE 'LOG'
	) MYLOGS
	ON MYROWS.DATABASENAME = MYROWS.DATABASENAME
GROUP BY 
	MYROWS.DATABASENAME

USE [master]

SELECT

MYROWS.DATABASENAME,

SUM(MYROWS.FILESIZE) AS DATA_FILESIZE, SUM(MYROWS.USEDSPACE) AS DATA_USEDSPACE, SUM(MYROWS.FREESPACE) AS DATA_FREESPACE, SUM(MYROWS.[FREESPACE_%]) AS DATA_FREESPACE_PERCENT,

SUM(MYLOGS.FILESIZE) AS LOG_FILESIZE, SUM(MYLOGS.USEDSPACE) AS LOG_USEDSPACE, SUM(MYLOGS.FREESPACE) AS LOG_FREESPACE, SUM(MYLOGS.[FREESPACE_%]) AS LOG_FREESPACE_PERCENT

FROM

(

SELECT

DB_NAME() AS DATABASENAME

,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024

,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024

,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024

,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)

FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id

WHERE A.type_desc LIKE 'ROWS'

) MYROWS

LEFT JOIN

(

SELECT

DB_NAME() AS DATABASENAME

,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024

,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024

,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024

,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)

FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id

WHERE A.type_desc LIKE 'LOG'

) MYLOGS

ON MYROWS.DATABASENAME = MYROWS.DATABASENAME

GROUP BY

MYROWS.DATABASENAME

May 21, 2019 by Florian Rossmark monitoring prtg server

Raspberry PI and Microsoft SQL databases

Raspberry PI can read and write on a Microsoft SQL server database.

In order to accomplish this you follow the instructions here: http://pymssql.org/en/stable/index.html

To summarize it in a nutshell, here is what you need to do:

apt-get install freetds-dev
pip install pymssql

Update: Above information is for a Raspberry 2 – Raspberry 3 needs the below information as far as I know:

sudo apt-get install freetds-dev
sudo pip3 install cython
sudo pip3 install pymssql

Personally I had issues getting this to work in Python 3.x so I tested it in Python 2.x and it was working fine. The issue was simply that the module “pymssql” could not be found and therefor the IMPORT line already failed in the Python script. It should be a rather easy fix – like copying the files to the Python 3 modules folder, but as of now I did not have the time to investigate this further – as I was fine using Python 2 in my specific situation.

Here is a sample script

import pymssql

server = "mySQLservername"
user = "myuser"
password = "mypassword"
DB = "mydatabase"

conn = pymssql.connect(server, user, password, DB)
cur = conn.cursor()
cur.execute('SELECT * FROM MyTable')
  for row in cur:
    #print(row['id'])
    print(row[0])
conn.close

import pymssql

server = "mySQLservername"

user = "myuser"

password = "mypassword"

DB = "mydatabase"

conn = pymssql.connect(server, user, password, DB)

cur = conn.cursor()

cur.execute('SELECT * FROM MyTable')

for row in cur:

#print(row['id'])

print(row[0])

conn.close

The example I tested used a SQL server user account. The documentation of PyMSSQL talks about the possibility to use Windows Authentication as well.

As for asking Google about this – there is a lot of confusion information out there – the top ranked posts aren’t really helpful, so I thought I just post it again hoping someone finds this helpful.

January 31, 2019 by Florian Rossmark configuration solutions

SQL Express SQLState 08001 and Error 17

One of the challenges especially with SQL Express is that you need to enable some protocols on the network level first in order to connect to it. You might see an error message the one below when you try to connect to SQL – stating SQLState 08001 and Error 17.

In order to resolve this, you need to enable named pipes and TCP in the SQL Server Configuration Manager that was installed by default on your system. See the image below on how it should look like. Please note that you need to restart the SQL service in order for those changes to take effect.

Please note – there might be a need of additional configuration like the Windows Firewall or other parameters, the above just addresses a rather common issue.

September 10, 2018 by Florian Rossmark configuration server solutions

Microsoft RADIUS / NPS SQL logging

An issue or question I see again and again – proper RADIUS logging with Microsoft NPS / Network Policy Server.

Let’s guide you through a few steps

Install a Microsoft SQL or if not available SQL Express
1. be aware – SQL Express has very tight database size limits and no SQL Agent – this might be an issue
Create a new database via SQL Management Studio in the SQL server
1. name it e.g. RADIUSLogging
run the SQL script from this Microsoft website in a new query window against this database (make sure it is not run against any other database by accident)
1. you could add a line like USE RADIUSLogging to prevent this – in the very top…
configure your RADIUS server to log to this SQL server and database
make sure you have fail-over logging to a text-file – to avoid issues in case your SQL DB grew to big or was not reachable for any reason
1. decide in the text-file configuration if you want to deny access if there is an issue or if you still want to proceed with the logon

Now you have RADIUS logging the information to a SQL database – actually a single table – and you can dig around in it. The IT-Assets database provides a front-end example for this – you don’t need to use it – but it might be of help – see here.

To interpret all those columns and values – look at the following links for additional information:

You will face the issue that the database will grow rapidly – depending on how many requests go to your RADIUS system etc… Keep an close eye on it – use a monitoring software like Paessler / PRTG to monitor the size and keep in mind that SQL Express might have size limits like 10 GB. The full version of Microsoft SQL has no such limits and further can you use SQL Agent to execute tasks. The following script can help you purging data from the RADIUS database to keep its size under control. You can use SQL Agent (not in SQL Express) to run it automatically or if you use SQL Express either run it manually or with another solution somehow automatically against the database delete older entries.

The script actually will purge data older then 14 days – you can adjust the days to your liking / needs.

DELETE FROM dbo.accounting_data
where datediff(day,dbo.accounting_data.timestamp,getdate()) >14

1 2	DELETE FROM dbo.accounting_data where datediff(day,dbo.accounting_data.timestamp,getdate()) >14

September 6, 2018 by Florian Rossmark configuration recommendations security server solutions

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

June 26, 2018 by Florian Rossmark scripts server solutions

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

Solarwinds Web Helpdesk – Slack alerts

This was originally posted here by myself: https://thwack.solarwinds.com/thread/114863

Solarwinds WebHelpDesk is very powerful, but for those who use Slack as communication and alerting platform, there still is no integration.

We as a IT team struggled a bit keeping up with the immense flow of emails and filtering them out as well as being really pro-active on new tickets (first response time) as well as realizing we got new tickets assigned or a user / client wrote a new note.

To overcome those challenges and due to the fact that we all have Slack on all our systems from workstation to smartphone, we decided to integrate this. Since I spend a bit of time on those scripts and thought they might be helpful for others as well, I am sharing them here now and explain on how to implement this.

Please note: Those scripts are a version 1 – I am very aware that they could be further cleaned up and simplified.. but I wanted to share them already… bugs are possible as well…

Requirements:

You need Solarwinds WebHelpDesk
The scripts use the field “Pager” in “Techs” for the Slack-Username – put all the Slack-Usernames of your Techs in this field – no @ – just the name
The scripts assumes you are executing it directly on the server that has the PostgreSQL database installed
The scripts assumes the database user/pw is defaulted to WHD

How to implement them:

Create the 5x files as show further below in e.g. “C:\Scripts\WHD” on the PostresSQL server
Create a new Windows Task that starts the WebHelpDesk_SlackAlerts.cmd file every 30 minutes
- this file actually executes the PowerShell Scripts – it is just a work-around – it bypasses any PowerShell Script execution restritions
download the PostgesSQL ODBC drivers from the following link – assuming you haven’t installed them on the system already
- PostgreSQL: File Browser
- Text link: https://www.postgresql.org/ftp/odbc/versions/msi/
Edit the PRTGSlackWebHookNotificationPSv2.ps1
- This file was originally from our PRTG installation and modified there already. It was further modified for WHD alerts – I do not claim to have invented this script nor do I want to abuse any copyrights on it! source: www.paessler.com for monitoring solutions
- Line 41: adjust the URL for the FavIcon.ico to your external WebHelpDesk URL
Create a new WebHook-Application in your Slack Account
- Incoming Webhooks | Slack
- Text link: https://api.slack.com/incoming-webhooks
- You will need the WebHook URL for the scripts now
Edit the CheckNewTicketsFirstResponse.ps1
- This script posts to a generic channel in our case – we want to see new tickets as a group – assuming assigned tech is currently unavailable and couldn’t touch it…
- adjust the PostgresSQL settings – if needed – IP / Port / User etc…
- Line 7 – ticket_age_minutes = 88
  - this is a minute value – we alert on a Slack-Group “helpdesk” if there is a ticket older than 88 minutes – we fire the script every 30 minutes, so it could be up to two hours old..
- Line 9 – $channel – adjust this to the Slack-Group channel you want to use for those alerts
- Line 77 – adjust the URL to your external WebHelpdesk URL
- Line 99 – adjust the Link to your own WebHook URL
Edit the CheckTicketAssigned.ps1
- This will send the message to the Tech directly through the SlackBot channel – only he will see it
- This will only fire if the Tech did not yet put a Tech-Note in the ticket after it was assigned to him
- adjust the PostgresSQL settings – if needed – IP / Port / User etc…
- Line 7 – entry_age_minutes = 32
  - this is a minute value – we run the scripts every 30 minutes – the alert due to some variable time can not be older than 32 minutes by default…
- Line 74 – adjust the URL to your external WebHelpdesk URL
- Line 97 – adjust the Link to your own WebHook URL
Edit the CheckTicketNewClientNote.ps1
- This will send the message to the Tech directly through the SlackBot channel – only he will see it
- This will only fire if the Tech did not yet put a Tech-Note in the ticket after the client / user did leave a comment or note
- adjust the PostgresSQL settings – if needed – IP / Port / User etc…
- Line 7 – entry_age_minutes = 32
  - this is a minute value – we run the scripts every 30 minutes – the alert due to some variable time can not be older than 32 minutes by default…
- Line 74 – adjust the URL to your external WebHelpdesk URL
- Line 97 – adjust the Link to your own WebHook URL

After that you should be all set.

We have further integration in to PRTG while monitoring the database for “new tickets older than 120 minutes” and looking for the logfile “SlackLogErrorDetails.txt” indicating that a Slack notification did not go through by the script – most likely due to special characters that the scripts already should take care off, but in case it happens again this file would appear. You can integrate that as well with your monitoring system, but this is beyond the scope of the simple notifications.

Other logfiles show just what was send out and indicate that everything is working well.

Click here to download the scripts archive WebHelpdeskSlackFiles.zip

June 1, 2018 by Florian Rossmark scripts solutions

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

database

Auditing network users against HR lists etc.

Structured groups and rights

Monitoring Active Directory activity

Auditing your user base against HR (Human Resources) data

Don’t forget your ERP systems and systems with Active Directory independent user bases

Office 365

Summarize SQL server database file size information as a single row

Raspberry PI and Microsoft SQL databases

SQL Express SQLState 08001 and Error 17

Microsoft RADIUS / NPS SQL logging

Automate your SUS clean up

Script based SQL Express backups

Solarwinds Web Helpdesk – Slack alerts

SQL Database backup monitoring

Recent blog posts

Blog Archives

database

Structured groups and rights

Monitoring Active Directory activity

Auditing your user base against HR (Human Resources) data

Don’t forget your ERP systems and systems with Active Directory independent user bases

Office 365

Recent blog posts

Blog Archives

Tags