monitoring

Monitor user accounts in Active Directory with PRTG

The following script will read through your current Active Directory and filter for user accounts with the following specific conditions:

Lockedout users – please read below for further information about this
- all users that are lockedout
- must be an enabled user
- that is not expired
disabled users
- all users that have been disabled
expired users
- must be an enabled user
- the expiration date is set and past the current date
users with password never expires set
- must be an enabled user

This will give you a pure counter output per channel in an for PRTG Extended script sensor XML result.

But there is a theoretical flaw in one of the methods – the locked out users. Now, user accounts get locked out in Active Directory due to too many logon attempts with an invalid password. This causes Active Directory to set the lockedout bit in the object properties. The issue here is that this bit will not be set back to 0 after the defined lockout duration (GPO) is past, the property will only be set back to 0 once the lockout duration is passed and he successfully logged on.

This means, the counter might give you more results then currently true, it might count users that have been locked out but the lockout-duration passed – but they did not yet logon successfully. This is somehow a false positive, while not totally false. In any case, you need to be aware of this.

The script could be more efficient as well in the way it filters a few things, so far I optimized it as far as I could – the LockedOut value can not be set as a -Filter, in theory it might be possible to speed it up with a -Filter to the UserAccountControl (if that is even possible – not tested) – but I am not certain this would work. If you really want to speed it up you would need to work with -LDAPFilter – but this actually needs to completely replace the internal filter capabilities of Get-ADUser – you can’t use both – it is one or the other.

Import-Module ActiveDirectory

#you could add - filters, a OU limitation or a server against whom this would be executed.. see Get-ADUser options for more details
#$Server = ""
#$OU = ""
#Get-ADUser -Server $Server -Filter
#Get-ADUser -SearchBase $OU -Filter

#all locked users that aren't disabled or expired
$LockedOutUsers=Get-ADUser -Filter {Enabled -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate | where {$_.lockedout -eq $True -and (($_.AccountExpirationDate -gt (Get-Date) -or ($_.AccountExpirationDate -eq $null)))} 

#all users that are disabled - this is a manual action in Active Directory
$DisabledUsers=Get-ADUser -Filter {Enabled -eq $False -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate

#all users that are not disabled but expired already
$ExpiredUsers=Get-ADUser -Filter {Enabled -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate | where {(($_.AccountExpirationDate -lt (Get-Date) -and ($_.AccountExpirationDate -ne $null)))} 

#users with not expiring passwords and are enabled and not expired
$NotExpiringPWD=Get-ADUser -Filter {Enabled -eq $True -and PasswordNeverExpires -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate 


If ($LockedOutUsers.count -eq $null -and $LockedOutUsers -eq $null){
    $cntLockedOutUsers=0
}Elseif ($LockedOutUsers.count -eq $null -and $LockedOutUsers -ne $null){
    $cntLockedOutUsers=1
}Else{
    $cntLockedOutUsers=@($LockedOutUsers.count)
}

If ($DisabledUsers.count -eq $null -and $DisabledUsers -eq $null){
    $cntDisabledUsers=0
}Elseif ($DisabledUsers.count -eq $null -and $DisabledUsers -ne $null){
    $cntDisabledUsers=1
}Else{
    $cntDisabledUsers=@($DisabledUsers.count)
}

If ($ExpiredUsers.count -eq $null -and $ExpiredUsers -eq $null){
    $cntExpiredUsers=0
}Elseif ($ExpiredUsers.count -eq $null -and $ExpiredUsers -ne $null){
    $cntExpiredUsers=1
}Else{
    $cntExpiredUsers=@($ExpiredUsers.count)
}

If ($NotExpiringPWD.count -eq $null -and $NotExpiringPWD -eq $null){
    $cntNotExpiringPWD=0
}Elseif ($NotExpiringPWD.count -eq $null -and $NotExpiringPWD -ne $null){
    $cntNotExpiringPWD=1
}Else{
    $cntNotExpiringPWD=@($NotExpiringPWD.count)
}

$XML += "<prtg>"
$XML += "<result>" 
$XML += "<channel>Locked Out Users</channel>" 
$XML += "<value>$cntLockedOutUsers</value>" 
$XML += "</result>"
$XML += "<result>" 
$XML += "<channel>Disabled Users</channel>" 
$XML += "<value>$cntDisabledUsers</value>" 
$XML += "</result>"
$XML += "<result>" 
$XML += "<channel>Expired Users - not disabled</channel>" 
$XML += "<value>$cntExpiredUsers</value>" 
$XML += "</result>"
$XML += "<result>" 
$XML += "<channel>Users with password never expires</channel>" 
$XML += "<value>$cntNotExpiringPWD</value>" 
$XML += "</result>"
$XML += "</prtg>"

Function WriteXmlToScreen ([xml]$xml)
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}

WriteXmlToScreen $XML

Import-Module ActiveDirectory

#you could add - filters, a OU limitation or a server against whom this would be executed.. see Get-ADUser options for more details

#$Server = ""

#$OU = ""

#Get-ADUser -Server $Server -Filter

#Get-ADUser -SearchBase $OU -Filter

#all locked users that aren't disabled or expired

$LockedOutUsers=Get-ADUser -Filter {Enabled -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate | where {$_.lockedout -eq $True -and (($_.AccountExpirationDate -gt (Get-Date) -or ($_.AccountExpirationDate -eq $null)))}

#all users that are disabled - this is a manual action in Active Directory

$DisabledUsers=Get-ADUser -Filter {Enabled -eq $False -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate

#all users that are not disabled but expired already

$ExpiredUsers=Get-ADUser -Filter {Enabled -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate | where {(($_.AccountExpirationDate -lt (Get-Date) -and ($_.AccountExpirationDate -ne $null)))}

#users with not expiring passwords and are enabled and not expired

$NotExpiringPWD=Get-ADUser -Filter {Enabled -eq $True -and PasswordNeverExpires -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate

If ($LockedOutUsers.count -eq $null -and $LockedOutUsers -eq $null){

$cntLockedOutUsers=0

}Elseif ($LockedOutUsers.count -eq $null -and $LockedOutUsers -ne $null){

$cntLockedOutUsers=1

}Else{

$cntLockedOutUsers=@($LockedOutUsers.count)

}

If ($DisabledUsers.count -eq $null -and $DisabledUsers -eq $null){

$cntDisabledUsers=0

}Elseif ($DisabledUsers.count -eq $null -and $DisabledUsers -ne $null){

$cntDisabledUsers=1

}Else{

$cntDisabledUsers=@($DisabledUsers.count)

}

If ($ExpiredUsers.count -eq $null -and $ExpiredUsers -eq $null){

$cntExpiredUsers=0

}Elseif ($ExpiredUsers.count -eq $null -and $ExpiredUsers -ne $null){

$cntExpiredUsers=1

}Else{

$cntExpiredUsers=@($ExpiredUsers.count)

}

If ($NotExpiringPWD.count -eq $null -and $NotExpiringPWD -eq $null){

$cntNotExpiringPWD=0

}Elseif ($NotExpiringPWD.count -eq $null -and $NotExpiringPWD -ne $null){

$cntNotExpiringPWD=1

}Else{

$cntNotExpiringPWD=@($NotExpiringPWD.count)

}

$XML += "<prtg>"

$XML += "<result>"

$XML += "<channel>Locked Out Users</channel>"

$XML += "<value>$cntLockedOutUsers</value>"

$XML += "</result>"

$XML += "<result>"

$XML += "<channel>Disabled Users</channel>"

$XML += "<value>$cntDisabledUsers</value>"

$XML += "</result>"

$XML += "<result>"

$XML += "<channel>Expired Users - not disabled</channel>"

$XML += "<value>$cntExpiredUsers</value>"

$XML += "</result>"

$XML += "<result>"

$XML += "<channel>Users with password never expires</channel>"

$XML += "<value>$cntNotExpiringPWD</value>"

$XML += "</result>"

$XML += "</prtg>"

Function WriteXmlToScreen ([xml]$xml)

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

WriteXmlToScreen $XML

This script updated with a corrected version as of February 2019 and was also posted in the PRTG knowledge base here.

Monitor DFS replication backlog between servers in PRTG

One of the challenges with DFS is to monitor the DFS replication backlog. There are various scripts out there to accomplish this. Unfortunately I found nothing I really liked and giving me the simple insight I wanted.

The goal was simple – a script that will monitor the backlog between two systems in both directions – meaning Server-A to Server-B and Server-B to Server-A. For both directions I wanted to see the amount of files as well as the size of those files. I did not care about what DFS groups and or DFS folders are affected in detail – this is because the amount of groups might change, the amount of folders will likely change rather frequently, meaning it would be a challenge to monitor it per group or even on a folder level very efficient. Monitoring the amounts of groups and folders alone has no really advantage, cause this would have been changed by an administrator.

Below you will find my script that actually expects three parameter – the two server names and a limit integer value. The limit will not influence the XML response of the script, you could add the <text>$Response</text> and <text>$Response2</text> tags in lines 77 and 79 after the </unit> and before the </result> tag if you want, I removed them currently.

See the picture below as an example of how the result looks like in PRTG.

Create the following script in C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML and make sure you have the C:\Windows\system32\WindowsPowerShell\v1.0\Modules\Dfsr\ and the C:\Windows\SysWow64\WindowsPowerShell\v1.0\Modules\Dfsr\ folders – you might need to copy them over. If you miss them at all, you might need to add the needed Windows Roles/Features or install the RSAT (Remote Server Administration Tools) on your system.

Param(
$SourceComputer=$args[0],
$DestinationComputer=$args[1],
$MaxBackLog=$args[2]
)

Import-Module Dfsr
#Import-Module -FullyQualifiedName "C:\Windows\system32\WindowsPowerShell\v1.0\Modules\Dfsr\DfsrPowerShell.dll"
#Module needs to be in System32 and SysWow64 directory - make sure the path exists in both cases

$Count=-1
$TotalSize=0
$Count2=-1
$TotalSize2=0
$Response="-1:Script did not execute right"
$Response2="-1:Script did not execute right"

$err = $null 
$BackLog = Get-DfsrBacklog -SourceComputerName "$SourceComputer" -DestinationComputerName "$DestinationComputer" -ea SilentlyContinue -ev err; 
If ($err -ne $null) 
{ 
$Response="-2:Script error with GET-DFSRBACKLOG"
} 
Else
{
$Response = "-3:Error getting count"
$Count = $BackLog.Count
If ($Count -gt $MaxBackLog)
{
$Response = "Alarm - Backlog is over defined maximum of $MaxBackLog"
}
Else
{
$Response = "OK"
}
ForEach($Item in $BackLog)
{ 
$file = $Item.FullPathName
$file = $file -replace ":", "$"
$file = "\\$SourceComputer\$file"
If (Test-Path $file)
{
$TotalSize += (Get-Item $file).Length
}
}
}
$err = $null 
$BackLog2 = Get-DfsrBacklog -DestinationComputerName "$SourceComputer" -SourceComputerName "$DestinationComputer" -ea SilentlyContinue -ev err; 
If ($err -ne $null) 
{ 
$Response2="-2:Script error with GET-DFSRBACKLOG"
} 
Else
{
$Response2 = "-3:Error getting count"
$Count2 = $BackLog2.Count
If ($Count2 -gt $MaxBackLog)
{
$Response2 = "Alarm - Backlog is over defined maximum of $MaxBackLog"
}
Else
{
$Response2 = "OK"
}
ForEach($Item in $BackLog2)
{ 
$file = $Item.FullPathName
$file = $file -replace ":", "$"
$file = "\\$DestinationComputer\$file"
If (Test-Path $file)
{
$TotalSize2 += (Get-Item $file).Length
}
}
}
$XML = "<prtg>"
$XML += "<result><channel>BackLog count 1</channel><value>$Count</value><unit>Count</unit></result>"
$XML += "<result><channel>BackLog size 1</channel><value>$TotalSize</value><unit>BytesFile</unit></result>"
$XML += "<result><channel>BackLog count 2</channel><value>$Count2</value><unit>Count</unit></result>"
$XML += "<result><channel>BackLog size 2</channel><value>$TotalSize2</value><unit>BytesFile</unit></result>"
$XML += "</prtg>"

Function WriteXmlToScreen ([xml]$xml)
{
$StringWriter = New-Object System.IO.StringWriter;
$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
$XmlWriter.Formatting = "indented";
$xml.WriteTo($XmlWriter);
$XmlWriter.Flush();
$StringWriter.Flush();
Write-Output $StringWriter.ToString();
}

WriteXmlToScreen $XML

Param(

$SourceComputer=$args[0],

$DestinationComputer=$args[1],

$MaxBackLog=$args[2]

)

Import-Module Dfsr

#Import-Module -FullyQualifiedName "C:\Windows\system32\WindowsPowerShell\v1.0\Modules\Dfsr\DfsrPowerShell.dll"

#Module needs to be in System32 and SysWow64 directory - make sure the path exists in both cases

$Count=-1

$TotalSize=0

$Count2=-1

$TotalSize2=0

$Response="-1:Script did not execute right"

$Response2="-1:Script did not execute right"

$err = $null

$BackLog = Get-DfsrBacklog -SourceComputerName "$SourceComputer" -DestinationComputerName "$DestinationComputer" -ea SilentlyContinue -ev err;

If ($err -ne $null)

{

$Response="-2:Script error with GET-DFSRBACKLOG"

}

Else

{

$Response = "-3:Error getting count"

$Count = $BackLog.Count

If ($Count -gt $MaxBackLog)

{

$Response = "Alarm - Backlog is over defined maximum of $MaxBackLog"

}

Else

{

$Response = "OK"

}

ForEach($Item in $BackLog)

{

$file = $Item.FullPathName

$file = $file -replace ":", "$"

$file = "\\$SourceComputer\$file"

If (Test-Path $file)

{

$TotalSize += (Get-Item $file).Length

}

$err = $null

$BackLog2 = Get-DfsrBacklog -DestinationComputerName "$SourceComputer" -SourceComputerName "$DestinationComputer" -ea SilentlyContinue -ev err;

If ($err -ne $null)

{

$Response2="-2:Script error with GET-DFSRBACKLOG"

}

Else

{

$Response2 = "-3:Error getting count"

$Count2 = $BackLog2.Count

If ($Count2 -gt $MaxBackLog)

{

$Response2 = "Alarm - Backlog is over defined maximum of $MaxBackLog"

}

Else

{

$Response2 = "OK"

}

ForEach($Item in $BackLog2)

{

$file = $Item.FullPathName

$file = $file -replace ":", "$"

$file = "\\$DestinationComputer\$file"

If (Test-Path $file)

{

$TotalSize2 += (Get-Item $file).Length

}

$XML = "<prtg>"

$XML += "<result><channel>BackLog count 1</channel><value>$Count</value><unit>Count</unit></result>"

$XML += "<result><channel>BackLog size 1</channel><value>$TotalSize</value><unit>BytesFile</unit></result>"

$XML += "<result><channel>BackLog count 2</channel><value>$Count2</value><unit>Count</unit></result>"

$XML += "<result><channel>BackLog size 2</channel><value>$TotalSize2</value><unit>BytesFile</unit></result>"

$XML += "</prtg>"

Function WriteXmlToScreen ([xml]$xml)

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

WriteXmlToScreen $XML

July 10, 2018 by Florian Rossmark monitoring prtg scripts server solutions

Monitoring Shadow-Copies with PRTG

This was originally posted here by myself: https://kb.paessler.com/en/topic/65026-monitor-shadow-copies-age#reply-247626

This is my solution for it – we monitor specific drives we enabled for shadow copy and wanted to see amount of shadows, newest should be within x hours and oldest should be at a minimum n hours – those limits can be configured with the limitations rather easily.

Main issue is – we talk about WMI modules that are only available in x64 if you use a x64 system. Now PRTG is executing sensors in x86, even thought it is installed on x64. Now, played around a while and came up with this simple solution.

Parameters for the parser-script (the one you need to execute) are: %host C: %host D: etc.

Parser script, needs to be in EXEXML directory: Name: Get-ShadowCopyStatsXMLx64parser.cmd

@"%SystemRoot%\Sysnative\WindowsPowerShell\v1.0\powershell.exe" -c "&'C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML\Get-ShadowCopyStatsXML.ps1' -Servername %1 -Driveletter %2"

1	@"%SystemRoot%\Sysnative\WindowsPowerShell\v1.0\powershell.exe" -c "&'C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML\Get-ShadowCopyStatsXML.ps1' -Servername %1 -Driveletter %2"

PS1 script, should to be in EXEXML directory – if not, adjust path in parser script: Name: Get-ShadowCopyStatsXML.ps1

Param(
	$ServerName,
    $DriveLetter
)
Begin
{	$script:CurrentErrorActionPreference = $ErrorActionPreference
    $ShadowCopyStats = @()

	Function GetShadowCopyStats
	{	Param($Computer)

try
{
        Import-Module @("Microsoft.PowerShell.Management","PSDiagnostics")
		$WMITarget = "$Computer"
		Get-WmiObject -Class "Win32_ComputerSystem" -Property "Name" -ComputerName $WMITarget | out-null
		If ($? -eq $False)
		{	$bWMIConnection = $False
			$WMITarget = "$Computer."
			Get-WmiObject -Class "Win32_ComputerSystem" -Property "Name" -ComputerName $WMITarget | out-null
			If($? -eq $False){$bWMIConnection = $False}Else{$bWMIConnection = $True}
		}

            $xml = '<prtg>'
                $Volumes = gwmi Win32_Volume -Property SystemName,DriveLetter,DeviceID -Filter "DriveType=3" -ComputerName $WMITarget |
			        Select SystemName,@{n="DriveLetter";e={$_.DriveLetter.ToUpper()}},DeviceID | Sort DriveLetter

                $ShadowCopies = gwmi Win32_ShadowCopy -Property VolumeName,InstallDate,Count -ComputerName $WMITarget |
				        Select VolumeName,InstallDate,Count,
				        @{n="CreationDate";e={$_.ConvertToDateTime($_.InstallDate)}}

} 
catch [Exception]
{
    #Write-Warning $_.Exception|format-list -force
$Error[0].Exception.StackTrace
$Error[0].Exception.InnerException.StackTrace
$Error[0].StackTrace
write-error "hello"
}

		        If($Volumes)
		        {
                    ForEach($Volume in $Volumes)
		            {
                        If($Volume.DriveLetter -eq $DriveLetter) {

                    
				            $VolumeShares = $VolumeShadowStorage = $DiffVolume = $VolumeShadowCopies = $Null
				            $VolumeShadowCopies = $ShadowCopies | ?{$_.VolumeName -eq $Volume.DeviceID} | Sort InstallDate

				            If($VolumeShadowCopies)
				            {
                                $xml += '<result><channel>ShadowCopyCount</channel><value>' + (($VolumeShadowCopies | Measure-Object -Property Count -Sum).Sum) + '</value></result>'
                                
                                $AgeOldest = New-TimeSpan -Start (($VolumeShadowCopies | Select -First 1).CreationDate) -End (Get-Date) 
                                $AgeLatest = New-TimeSpan -Start (($VolumeShadowCopies | Select -Last 1).CreationDate) -End (Get-Date) 
                        
                                $xml += '<result><channel>OldestShadowCopy</channel><value>' + [math]::Round($AgeOldest.TotalHours,0) + '</value><unit>TimeHours</unit></result>'
                                $xml += '<result><channel>LatestShadowCopy</channel><value>' + [math]::Round($AgeLatest.TotalHours,0) + '</value><unit>TimeHours</unit></result>'
				            }Else{
                                $xml += '<result><channel>ShadowCopyCount</channel><value>0</value></result>'
                                $xml += '<result><channel>OldestShadowCopy</channel><value>0</value><unit>TimeHours</unit></result>'
                                $xml += '<result><channel>LatestShadowCopy</channel><value>0</value><unit>TimeHours</unit></result>'
                            }
				            If($VolumeShadowStorage -Or $ShowAllVolumes){$Output += $Object}
                        }
		            }
                }
            $xml += '</prtg>'
            WriteXmlToScreen $xml
        
	}


    Function WriteXmlToScreen ([xml]$xml)
    {
        $StringWriter = New-Object System.IO.StringWriter;
        $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
        $XmlWriter.Formatting = "indented";
        $xml.WriteTo($XmlWriter);
        $XmlWriter.Flush();
        $StringWriter.Flush();
        Write-Output $StringWriter.ToString();
    }
}
Process
{	If($ServerName)
	{ForEach($Server in $ServerName){$ShadowCopyStats += GetShadowCopyStats $Server}}
	Else
	{$ShadowCopyStats += GetShadowCopyStats $_}
}
End
{	$ErrorActionPreference = $script:CurrentErrorActionPreference
	$ShadowCopyStats
}

Param(

$ServerName,

$DriveLetter

)

Begin

{ $script:CurrentErrorActionPreference = $ErrorActionPreference

$ShadowCopyStats = @()

Function GetShadowCopyStats

{ Param($Computer)

try

{

Import-Module @("Microsoft.PowerShell.Management","PSDiagnostics")

$WMITarget = "$Computer"

Get-WmiObject -Class "Win32_ComputerSystem" -Property "Name" -ComputerName $WMITarget | out-null

If ($? -eq $False)

{ $bWMIConnection = $False

$WMITarget = "$Computer."

Get-WmiObject -Class "Win32_ComputerSystem" -Property "Name" -ComputerName $WMITarget | out-null

If($? -eq $False){$bWMIConnection = $False}Else{$bWMIConnection = $True}

}

$xml = '<prtg>'

$Volumes = gwmi Win32_Volume -Property SystemName,DriveLetter,DeviceID -Filter "DriveType=3" -ComputerName $WMITarget |

Select SystemName,@{n="DriveLetter";e={$_.DriveLetter.ToUpper()}},DeviceID | Sort DriveLetter

$ShadowCopies = gwmi Win32_ShadowCopy -Property VolumeName,InstallDate,Count -ComputerName $WMITarget |

Select VolumeName,InstallDate,Count,

@{n="CreationDate";e={$_.ConvertToDateTime($_.InstallDate)}}

}

catch [Exception]

{

#Write-Warning $_.Exception|format-list -force

$Error[0].Exception.StackTrace

$Error[0].Exception.InnerException.StackTrace

$Error[0].StackTrace

write-error "hello"

}

If($Volumes)

{

ForEach($Volume in $Volumes)

{

If($Volume.DriveLetter -eq $DriveLetter) {

$VolumeShares = $VolumeShadowStorage = $DiffVolume = $VolumeShadowCopies = $Null

$VolumeShadowCopies = $ShadowCopies | ?{$_.VolumeName -eq $Volume.DeviceID} | Sort InstallDate

If($VolumeShadowCopies)

{

$xml += '<result><channel>ShadowCopyCount</channel><value>' + (($VolumeShadowCopies | Measure-Object -Property Count -Sum).Sum) + '</value></result>'

$AgeOldest = New-TimeSpan -Start (($VolumeShadowCopies | Select -First 1).CreationDate) -End (Get-Date)

$AgeLatest = New-TimeSpan -Start (($VolumeShadowCopies | Select -Last 1).CreationDate) -End (Get-Date)

$xml += '<result><channel>OldestShadowCopy</channel><value>' + [math]::Round($AgeOldest.TotalHours,0) + '</value><unit>TimeHours</unit></result>'

$xml += '<result><channel>LatestShadowCopy</channel><value>' + [math]::Round($AgeLatest.TotalHours,0) + '</value><unit>TimeHours</unit></result>'

}Else{

$xml += '<result><channel>ShadowCopyCount</channel><value>0</value></result>'

$xml += '<result><channel>OldestShadowCopy</channel><value>0</value><unit>TimeHours</unit></result>'

$xml += '<result><channel>LatestShadowCopy</channel><value>0</value><unit>TimeHours</unit></result>'

}

If($VolumeShadowStorage -Or $ShowAllVolumes){$Output += $Object}

}

$xml += '</prtg>'

WriteXmlToScreen $xml

}

Function WriteXmlToScreen ([xml]$xml)

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

Process

{ If($ServerName)

{ForEach($Server in $ServerName){$ShadowCopyStats += GetShadowCopyStats $Server}}

Else

{$ShadowCopyStats += GetShadowCopyStats $_}

}

End

{ $ErrorActionPreference = $script:CurrentErrorActionPreference

$ShadowCopyStats

}

PS: yes, the PS1 could be further optimized, but it took me already a while to find out the main issue was the x86/x64 combination, what is not possible – see Microsoft articles in MSDN/KB for more information while the Shadow-Copy WMI modules are only available in x64 on a x64 system.

Hope this helps others with the same challenge 🙂

June 1, 2018 by Florian Rossmark monitoring prtg scripts server

PRTG – Veritas Backup Exec monitoring

This was originally posted by myself here: https://kb.paessler.com/en/topic/58233-symantec-backupexec-monitoring#reply-262024

We monitor backups a little bit more advanced – I thought I should share this knowledge as well…

Single Job Monitoring: Monitor a single job and it’s results – allows you configure the Job-Name with a PRTG filter value in the SQL sensor. The results will include various values – most notable are:

FinalJobStatus (as text)
TotalDataSizeBytes
TotalNumberOfDirectories
TotalNumberOfFiles
TotalRateMBMin

In order to implement this sensor, add a SQLv2 Sensor and configure like this:

Database: BEDB
Instance Name: BKUPEXEC (in most cases)
Use input parameter: specify the exact job name
DBNull = Error

Channels: Most channels are rather simple to configure, they are counters, SpeedDisk or BytesDisk – as PRTG has those channel types integrated already. The special channel is FINALJOBSTATUS – in order to have this working you will need the “backupexec.jobstatus.ovl” file in your %programfilesx86%\PRTG…\Lookups directory – see below for the file.

SQL Script for single job monitoring:

SELECT TOP 1
	J.JobName,
	H.TotalDataSizeBytes, H.TotalRateMBMin, H.TotalNumberOfFiles, H.TotalNumberOfDirectories,
	H.FinalJobStatus,
	H.ActualStartTime, H.EndTime, H.ElapsedTimeSeconds

FROM Jobs J 
	LEFT JOIN TaskDefinition T ON T.JobDefinitionID = J.JobDefinitionID
	LEFT JOIN Schedule S ON S.ScheduleId = T.ScheduleID
	LEFT JOIN JobHistorySummary H ON H.JobID = J.JobID

WHERE S.SystemId IS NOT NULL
	AND H.ActualStartTime > GETDATE()-8 --adjust this value if needed, 8 helps for the last 8 days.. weekly backups (full backups)
	AND J.JobName LIKE @prtg

ORDER BY H.ActualStartTime DESC

SELECT TOP 1

J.JobName,

H.TotalDataSizeBytes, H.TotalRateMBMin, H.TotalNumberOfFiles, H.TotalNumberOfDirectories,

H.FinalJobStatus,

H.ActualStartTime, H.EndTime, H.ElapsedTimeSeconds

FROM Jobs J

LEFT JOIN TaskDefinition T ON T.JobDefinitionID = J.JobDefinitionID

LEFT JOIN Schedule S ON S.ScheduleId = T.ScheduleID

LEFT JOIN JobHistorySummary H ON H.JobID = J.JobID

WHERE S.SystemId IS NOT NULL

AND H.ActualStartTime > GETDATE()-8 --adjust this value if needed, 8 helps for the last 8 days.. weekly backups (full backups)

AND J.JobName LIKE @prtg

ORDER BY H.ActualStartTime DESC

The backupexec.jobstatus.ovl file:

<?xml version="1.0" encoding="UTF-8"?>
<ValueLookup id="backupexec.jobstatus" desiredValue="1" undefinedState="error" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd">
   <Lookups>
	<SingleInt state="warning" value="1">
		Job Cancelled
	</SingleInt>
	<SingleInt state="Ok" value="2">
		Job Completed
	</SingleInt>
	<SingleInt state="warning" value="3">
		Job Exception
	</SingleInt>
	<SingleInt state="warning" value="4">
		Job Dispatched
	</SingleInt>
	<SingleInt state="warning" value="5">
		Job On Hold
	</SingleInt>
	<SingleInt state="error" value="6">
		Job Error
	</SingleInt>
	<SingleInt state="error" value="7">
		Job Invalid Schedule
	</SingleInt>
	<SingleInt state="error" value="8">
		Job Invalid Time Window
	</SingleInt>
	<SingleInt state="error" value="9">
		unknown status code (9)
	</SingleInt>
	<SingleInt state="error" value="10">
		Job Missed
	</SingleInt>
	<SingleInt state="warning" value="11">
		Job Paused
	</SingleInt>
	<SingleInt state="warning" value="12">
		Job Pending
	</SingleInt>
	<SingleInt state="warning" value="13">
		Job Recovered
	</SingleInt>
	<SingleInt state="error" value="14">
		Job Resource Disabled
	</SingleInt>
	<SingleInt state="warning" value="15">
		Job Resumed
	</SingleInt>
	<SingleInt state="Ok" value="16">
		Job Running
	</SingleInt>
	<SingleInt state="Ok" value="17">
		Job Ready
	</SingleInt>
	<SingleInt state="Ok" value="18">
		Job Scheduled
	</SingleInt>
	<SingleInt state="Ok" value="19">
		Job Success
	</SingleInt>
	<SingleInt state="warning" value="20">
		Job Superceded
	</SingleInt>
	<SingleInt state="error" value="21">
		Job Abort (Time)
	</SingleInt>
	<SingleInt state="warning" value="22">
		Job To Be Scheduled
	</SingleInt>
	<SingleInt state="warning" value="23">
		Job Linked to Another Job
	</SingleInt>
	<SingleInt state="error" value="24">
		unknown status value (24)
	</SingleInt>
	<SingleInt state="error" value="25">
		Job Blocked by Template Rule
	</SingleInt>	
   </Lookups>
</ValueLookup>

<?xml version="1.0" encoding="UTF-8"?>

Job Cancelled

</SingleInt>

Job Completed

</SingleInt>

Job Exception

</SingleInt>

Job Dispatched

</SingleInt>

Job On Hold

</SingleInt>

Job Error

</SingleInt>

Job Invalid Schedule

</SingleInt>

Job Invalid Time Window

</SingleInt>

unknown status code (9)

</SingleInt>

Job Missed

</SingleInt>

Job Paused

</SingleInt>

Job Pending

</SingleInt>

Job Recovered

</SingleInt>

Job Resource Disabled

</SingleInt>

Job Resumed

</SingleInt>

Job Running

</SingleInt>

Job Ready

</SingleInt>

Job Scheduled

</SingleInt>

Job Success

</SingleInt>

Job Superceded

</SingleInt>

Job Abort (Time)

</SingleInt>

Job To Be Scheduled

</SingleInt>

Job Linked to Another Job

</SingleInt>

unknown status value (24)

</SingleInt>

Job Blocked by Template Rule

</SingleInt>

</Lookups>

</ValueLookup>

Another SQL script we use is the one below – this actually approaches the whole monitoring more in an overview – it still depends on the JobHistory table, meaning, the job must have been running.. in theory you could work around this and actually get information on the scheduler etc.. the script below is a pure example.

DECLARE @JobFilter AS VARCHAR(500)
DECLARE @TimeWindow AS Integer

SET @JobFilter = '%-FULL' -- we only want to see jobs that with "-FULL" at the end of the job name / use @PRGT if you want to control it in PRTG
SET @TimeWindow = 7 --7 days in each direction for history and scheduled jobs

SELECT 
	COUNT(J.JobName) AS TotalJobs,
	SUM(CASE WHEN H.IsJobActive = 1 THEN 1 ELSE 0 END) AS ActiveJobs,

--	SUM(CASE WHEN H.FinalJobStatus = 1 THEN 1 ELSE 0 END) AS FinalStatus_JobCancelled, 
--	SUM(CASE WHEN H.FinalJobStatus = 2 THEN 1 ELSE 0 END) AS FinalStatus_JobCompleted, 
--	SUM(CASE WHEN H.FinalJobStatus = 3 THEN 1 ELSE 0 END) AS FinalStatus_JobException, 
--	SUM(CASE WHEN H.FinalJobStatus = 4 THEN 1 ELSE 0 END) AS FinalStatus_JobDispatched,
--	SUM(CASE WHEN H.FinalJobStatus = 5 THEN 1 ELSE 0 END) AS FinalStatus_JobOnHold,
	SUM(CASE WHEN H.FinalJobStatus = 6 THEN 1 ELSE 0 END) AS FinalStatus_JobError, 
--	SUM(CASE WHEN H.FinalJobStatus = 7 THEN 1 ELSE 0 END) AS FinalStatus_JobInvalidSchedule,
--	SUM(CASE WHEN H.FinalJobStatus = 8 THEN 1 ELSE 0 END) AS FinalStatus_JobInvalidTimeWindow,
--	SUM(CASE WHEN H.FinalJobStatus = 9 THEN 1 ELSE 0 END) AS FinalStatus_unknownStatusCode9, 
--	SUM(CASE WHEN H.FinalJobStatus = 10 THEN 1 ELSE 0 END) AS FinalStatus_JobMissed, 
--	SUM(CASE WHEN H.FinalJobStatus = 11 THEN 1 ELSE 0 END) AS FinalStatus_JobPaused, 
--	SUM(CASE WHEN H.FinalJobStatus = 12 THEN 1 ELSE 0 END) AS FinalStatus_JobPending,
	--SUM(CASE WHEN H.FinalJobStatus = 13 THEN 1 ELSE 0 END) AS FinalStatus_JobRecovered, 
--	SUM(CASE WHEN H.FinalJobStatus = 14 THEN 1 ELSE 0 END) AS FinalStatus_JobResourceDisabled, 
--	SUM(CASE WHEN H.FinalJobStatus = 15 THEN 1 ELSE 0 END) AS FinalStatus_JobResumed, 
	SUM(CASE WHEN H.FinalJobStatus = 16 THEN 1 ELSE 0 END) AS FinalStatus_JobRunning, 
--	SUM(CASE WHEN H.FinalJobStatus = 17 THEN 1 ELSE 0 END) AS FinalStatus_JobReady, 
--	SUM(CASE WHEN H.FinalJobStatus = 18 THEN 1 ELSE 0 END) AS FinalStatus_JobScheduled, 
	SUM(CASE WHEN H.FinalJobStatus = 19 THEN 1 ELSE 0 END) AS FinalStatus_JobSuccess, 
	SUM(CASE WHEN H.FinalJobStatus = 20 THEN 1 ELSE 0 END) AS FinalStatus_JobSuperceded, 
--	SUM(CASE WHEN H.FinalJobStatus = 21 THEN 1 ELSE 0 END) AS FinalStatus_JobAbort, 
--	SUM(CASE WHEN H.FinalJobStatus = 22 THEN 1 ELSE 0 END) AS FinalStatus_JobToBeScheduled, 
--	SUM(CASE WHEN H.FinalJobStatus = 23 THEN 1 ELSE 0 END) AS FinalStatus_JobLinkedToAnotherJob, 
--	SUM(CASE WHEN H.FinalJobStatus = 24 THEN 1 ELSE 0 END) AS FinalStatus_unknownStatusCode24, 
--	SUM(CASE WHEN H.FinalJobStatus = 25 THEN 1 ELSE 0 END) AS FinalStatus_JobBlockedByTemplateTule, 

	SUM(CASE WHEN H.FinalJobStatus = 3 THEN 1 ELSE 0 END) AS FinalStatus_Exceptions --3 = With Exceptions
--	SUM(CASE WHEN H.FinalJobStatus = 19 THEN 1 ELSE 0 END) AS FinalStatus_SuccessFull --19 = Successful
	
FROM Jobs J 
	LEFT JOIN TaskDefinition T ON T.JobDefinitionID = J.JobDefinitionID
	LEFT JOIN Schedule S ON S.ScheduleId = T.ScheduleID
	LEFT JOIN JobHistorySummary H ON H.JobID = J.JobID

WHERE S.SystemId IS NOT NULL
	AND H.ActualStartTime > GETDATE() - @TimeWindow
	AND J.NextDueDate < GETDATE() + @TimeWindow
	AND J.JobName LIKE @JobFilter

DECLARE @JobFilter AS VARCHAR(500)

DECLARE @TimeWindow AS Integer

SET @JobFilter = '%-FULL' -- we only want to see jobs that with "-FULL" at the end of the job name / use @PRGT if you want to control it in PRTG

SET @TimeWindow = 7 --7 days in each direction for history and scheduled jobs

SELECT

COUNT(J.JobName) AS TotalJobs,

SUM(CASE WHEN H.IsJobActive = 1 THEN 1 ELSE 0 END) AS ActiveJobs,

-- SUM(CASE WHEN H.FinalJobStatus = 1 THEN 1 ELSE 0 END) AS FinalStatus_JobCancelled,

-- SUM(CASE WHEN H.FinalJobStatus = 2 THEN 1 ELSE 0 END) AS FinalStatus_JobCompleted,

-- SUM(CASE WHEN H.FinalJobStatus = 3 THEN 1 ELSE 0 END) AS FinalStatus_JobException,

-- SUM(CASE WHEN H.FinalJobStatus = 4 THEN 1 ELSE 0 END) AS FinalStatus_JobDispatched,

-- SUM(CASE WHEN H.FinalJobStatus = 5 THEN 1 ELSE 0 END) AS FinalStatus_JobOnHold,

SUM(CASE WHEN H.FinalJobStatus = 6 THEN 1 ELSE 0 END) AS FinalStatus_JobError,

-- SUM(CASE WHEN H.FinalJobStatus = 7 THEN 1 ELSE 0 END) AS FinalStatus_JobInvalidSchedule,

-- SUM(CASE WHEN H.FinalJobStatus = 8 THEN 1 ELSE 0 END) AS FinalStatus_JobInvalidTimeWindow,

-- SUM(CASE WHEN H.FinalJobStatus = 9 THEN 1 ELSE 0 END) AS FinalStatus_unknownStatusCode9,

-- SUM(CASE WHEN H.FinalJobStatus = 10 THEN 1 ELSE 0 END) AS FinalStatus_JobMissed,

-- SUM(CASE WHEN H.FinalJobStatus = 11 THEN 1 ELSE 0 END) AS FinalStatus_JobPaused,

-- SUM(CASE WHEN H.FinalJobStatus = 12 THEN 1 ELSE 0 END) AS FinalStatus_JobPending,

--SUM(CASE WHEN H.FinalJobStatus = 13 THEN 1 ELSE 0 END) AS FinalStatus_JobRecovered,

-- SUM(CASE WHEN H.FinalJobStatus = 14 THEN 1 ELSE 0 END) AS FinalStatus_JobResourceDisabled,

-- SUM(CASE WHEN H.FinalJobStatus = 15 THEN 1 ELSE 0 END) AS FinalStatus_JobResumed,

SUM(CASE WHEN H.FinalJobStatus = 16 THEN 1 ELSE 0 END) AS FinalStatus_JobRunning,

-- SUM(CASE WHEN H.FinalJobStatus = 17 THEN 1 ELSE 0 END) AS FinalStatus_JobReady,

-- SUM(CASE WHEN H.FinalJobStatus = 18 THEN 1 ELSE 0 END) AS FinalStatus_JobScheduled,

SUM(CASE WHEN H.FinalJobStatus = 19 THEN 1 ELSE 0 END) AS FinalStatus_JobSuccess,

SUM(CASE WHEN H.FinalJobStatus = 20 THEN 1 ELSE 0 END) AS FinalStatus_JobSuperceded,

-- SUM(CASE WHEN H.FinalJobStatus = 21 THEN 1 ELSE 0 END) AS FinalStatus_JobAbort,

-- SUM(CASE WHEN H.FinalJobStatus = 22 THEN 1 ELSE 0 END) AS FinalStatus_JobToBeScheduled,

-- SUM(CASE WHEN H.FinalJobStatus = 23 THEN 1 ELSE 0 END) AS FinalStatus_JobLinkedToAnotherJob,

-- SUM(CASE WHEN H.FinalJobStatus = 24 THEN 1 ELSE 0 END) AS FinalStatus_unknownStatusCode24,

-- SUM(CASE WHEN H.FinalJobStatus = 25 THEN 1 ELSE 0 END) AS FinalStatus_JobBlockedByTemplateTule,

SUM(CASE WHEN H.FinalJobStatus = 3 THEN 1 ELSE 0 END) AS FinalStatus_Exceptions --3 = With Exceptions

-- SUM(CASE WHEN H.FinalJobStatus = 19 THEN 1 ELSE 0 END) AS FinalStatus_SuccessFull --19 = Successful

FROM Jobs J

LEFT JOIN TaskDefinition T ON T.JobDefinitionID = J.JobDefinitionID

LEFT JOIN Schedule S ON S.ScheduleId = T.ScheduleID

LEFT JOIN JobHistorySummary H ON H.JobID = J.JobID

WHERE S.SystemId IS NOT NULL

AND H.ActualStartTime > GETDATE() - @TimeWindow

AND J.NextDueDate < GETDATE() + @TimeWindow

AND J.JobName LIKE @JobFilter

Finally I wanted to mention what are our real challenges are, and we don’t yet have a really good solution: Our backup runs FULL starting Friday evening… during the week we run incremental backups. Now the incremental backups are not as critical… so let’s focus on the weekends.

What happened every now and then was that e.g. only a few tapes where write able and other might still have been locked or one of our libraries jammed etc..

In the end, it means – we e.g. came in Monday morning and discovered that 50+ % of the backups did not run.

Now, the question is, how do you monitor this. There are about a 150 jobs – they are stacked on each other. In theory I expect let say 5 running jobs, 0 completed and a 145 pending – starting Friday night – over the weekend this number will change constantly.

What I did not yet find is that a good solution that when Backup Exec waits for user interaction like insert tapes, offline library, etc. does wait for user interaction.

As well as the fact that I can’t tell PRTG on Friday I expect e.g. 150 jobs pending, on Saturday 1 PM the number should be more like 75 jobs pending and on Sunday 6 AM is should be down to 50 pending and Sunday 8 PM it should be 0 pending and 150 successful.

This is very granular, making it hard to find a solution. The jobs in our case will not finish – they are within their weekend time-window and will not be auto cancelled and therefor only manually looking in to Backup Exec will tell you if we are making progress or not.

It could be a solution to constantly see if the Total Bytes backed up goes up – but this again is challenging, we would need to compare values over time.. PRTG is as far as I know not directly able to do so and this would mean we would need to have a temp file with values form the last check in some kind of script or database that we would compare too…

So far I did not come up with the ultimate solution – every now and then I think about it a little more.. but well, I am not there yet.

June 1, 2018 by Florian Rossmark monitoring prtg scripts server

Advanced PRTG Slack notifitications

This PRTG slack notification script was originally posted by myself here: https://kb.paessler.com/en/topic/66113-how-can-prtg-send-notifications-to-slack#reply-249147

This is our version of the script originally provided with PRTG by Paessler. – we are using the #colorstatus as well to determine to current issue and we are using actually icons to show the status – this is working pretty well for us. Besides that, reformatted the whole notification a bit so it is easier to read in the prtg slack notification messages.

It is easy to adjust and even use additional statuses/color-codes if needed..

# ----------------------------------------------------------------------------------------------
# Copyright (c) WCOM AB 2016.
# ----------------------------------------------------------------------------------------------
# This source code is subject to terms and conditions of the The MIT License (MIT)
# copy of the license can be found in the LICENSE file at the root of this distribution.
# ----------------------------------------------------------------------------------------------
# You must not remove this notice, or any other, from this software.
# ----------------------------------------------------------------------------------------------
Param(
    [string]$SlackWebHook,
    [string]$SlackChannel,
    [string]$SiteName,
    [string]$Device,
    [string]$Name,
    [string]$Status,
    [string]$Down,
    [string]$DateTime,
    [string]$LinkDevice,
    [string]$Message,
    [string]$Color
)
string $StatusIcon
string $SlackTextFormatting

Switch ($Color)
{
    '#b4cc38'
    {
        #UP
        $StatusIcon = ':white_check_mark:'
        $SlackTextFormatting = '*'
    }
    '#d71920'
    {
        #DOWN
        $StatusIcon = ':name_badge:'
        $SlackTextFormatting = '`'
    }
    '#ffcb05'
    {
        #WARNING
        $StatusIcon = ':radioactive_sign:'
        $SlackTextFormatting = '`'
    }
    '#447fc1'
    {
        #PAUSE
        $StatusIcon = ':double_vertical_bar:'
        $SlackTextFormatting = '~'
    }
    default 
    {
        $StatusIcon = ':interrobang:'
    }
}


Add-Type -AssemblyName System.Web.Extensions
function ConvertTo-Json ([Object] $value)
{
    [System.Web.Script.Serialization.JavaScriptSerializer] $jsSerializer = New-Object 'System.Web.Script.Serialization.JavaScriptSerializer'
    $jsSerializer.Serialize($value)
}

$postSlackMessage = ConvertTo-Json(@{
    channel      = $SlackChannel;
    unfurl_links = "true";
    username     = "PRTG";
    icon_url     = "https://www.paessler.com/common/files/banner/banner_3s_80x80.png"
    text         = "$($StatusIcon) <$($LinkDevice)|$($Device) $SlackTextFormatting$($Name)$SlackTextFormatting> 
>Status *$($Status) $($Down)* on $($DateTime) 
``````$($Message)``````";
    })

$postSlackMessage | Out-File -FilePath slack.log

[System.Net.WebClient] $webclient = New-Object 'System.Net.WebClient'
$webclient.UploadData($SlackWebHook, [System.Text.Encoding]::UTF8.GetBytes($postSlackMessag

# ----------------------------------------------------------------------------------------------

# Copyright (c) WCOM AB 2016.

# ----------------------------------------------------------------------------------------------

# This source code is subject to terms and conditions of the The MIT License (MIT)

# copy of the license can be found in the LICENSE file at the root of this distribution.

# ----------------------------------------------------------------------------------------------

# You must not remove this notice, or any other, from this software.

# ----------------------------------------------------------------------------------------------

Param(

[string]$SlackWebHook,

[string]$SlackChannel,

[string]$SiteName,

[string]$Device,

[string]$Name,

[string]$Status,

[string]$Down,

[string]$DateTime,

[string]$LinkDevice,

[string]$Message,

[string]$Color

)

string $StatusIcon

string $SlackTextFormatting

Switch ($Color)

{

'#b4cc38'

{

#UP

$StatusIcon = ':white_check_mark:'

$SlackTextFormatting = '*'

}

'#d71920'

{

#DOWN

$StatusIcon = ':name_badge:'

$SlackTextFormatting = '`'

}

'#ffcb05'

{

#WARNING

$StatusIcon = ':radioactive_sign:'

$SlackTextFormatting = '`'

}

'#447fc1'

{

#PAUSE

$StatusIcon = ':double_vertical_bar:'

$SlackTextFormatting = '~'

}

default

{

$StatusIcon = ':interrobang:'

}

Add-Type -AssemblyName System.Web.Extensions

function ConvertTo-Json ([Object] $value)

{

[System.Web.Script.Serialization.JavaScriptSerializer] $jsSerializer = New-Object 'System.Web.Script.Serialization.JavaScriptSerializer'

$jsSerializer.Serialize($value)

}

$postSlackMessage = ConvertTo-Json(@{

channel = $SlackChannel;

unfurl_links = "true";

username = "PRTG";

icon_url = "https://www.paessler.com/common/files/banner/banner_3s_80x80.png"

text = "$($StatusIcon) <$($LinkDevice)|$($Device) $SlackTextFormatting$($Name)$SlackTextFormatting>

>Status *$($Status) $($Down)* on $($DateTime)

``````$($Message)``````";

})

$postSlackMessage | Out-File -FilePath slack.log

[System.Net.WebClient] $webclient = New-Object 'System.Net.WebClient'

$webclient.UploadData($SlackWebHook, [System.Text.Encoding]::UTF8.GetBytes($postSlackMessag

June 1, 2018 by Florian Rossmark monitoring prtg scripts

Monitor your projectors and light bulbs with PJLink

Below is a solution rather than a question. Just thought I share it with the rest of the Paessler/PRTG world. This was originally posted here by myself: https://kb.paessler.com/en/topic/76639-monitor-your-projectors-and-light-bulbs-with-pjlink

Our challenge:

Projectors are having issues “out of now where” and of course always urgent – IT department didn’t have monitoring on it. Of course, normally you already have a conference going on or it is urgent and the projector might be really down already – most likely due to an end of life light bulb.

What we have:

NEC and Optima projectors – with Ethernet interfaces

Research and work:

We found out that there is a PJLink protocol (http://pjlink.jbmia.or.jp/english/index.html) that is supported by those devices. A little more research and we found a library on GitHub for Visual Studio (C#) (https://github.com/uow-dmurrell/ProjectorControl). We took it, modified the LAMPHOURS so we could get it public (only one lamp for now – but we only have projectors with one lamp). Modified the TEST-Project that comes with it to accept IP addresses as parameter and modified the output in to a PRTG XML format. Further modified the output so it is numeric. (We are not aware that any of that stuff is copyright protected, if so – we apologize and will stop using those components)

Result:

We added a PRTG sensor based on XML data that monitors (advanced EXE/XML) with the IP of the device as parameter and get the following information:

LampHours – we set ours to max. 4000 as error level
CoverStatus (0 OK / 1 ERROR)
FanStatus (0 OK / 1 ERROR)
FilterStatus (0 OK / 1 ERROR)
LampStatus (0 OK / 1 ERROR)
PowerStatus (0 off / 1 on = OK 2 cool down / 3 warm up = WARNING (we don’t get alerts there) 4 unknown = ERROR)

This actually helps us to keep an as close as possible eye on the projectors and being proactive if something is already reported as defect and the LampHours (adjustable, depending on manufacturer/model) are coming close to an issue, so we are ready for it.

Code changes:

ProjectorInfo.cs – added this:

        public int LampHours
        {
            get { return _multiLampHours[0]; }
            set {  }
        }

public int LampHours

{

get { return _multiLampHours[0]; }

set { }

}

Program.cs (or create your own project)

using System;
using rv; 

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            PJLinkConnection c = new PJLinkConnection(args[0], "rv");

            if (args.Length != 1)
            {
                Console.WriteLine("Wrong arguments detected:");
                foreach(string arg in args)
                {
                    Console.WriteLine(arg);
                }
            }

            ProjectorInfo pi = ProjectorInfo.create(c);

            string powerstatus = "0";
            string fanstatus = "0";
            string lampstatus = "0";
            string coverstatus = "0";
            string filterstatus = "0";

            //PowerStatus:
            //OFF 0
            //ON 1
            //COOLING 2
            //WARMUP 3
            //UNKNOWN 4
            if (pi.PowerStatus.ToString() == "OFF") { powerstatus = "0"; };
            if (pi.PowerStatus.ToString() == "ON") { powerstatus = "1"; };
            if (pi.PowerStatus.ToString() == "COOLING") { powerstatus = "2"; };
            if (pi.PowerStatus.ToString() == "WARMUP") { powerstatus = "3"; };
            if (pi.PowerStatus.ToString() == "UNKNOWN") { powerstatus = "4"; };

            if (pi.FanStatus.ToString() != "OK") { fanstatus = "1"; };
            if (pi.LampStatus.ToString() != "OK") { lampstatus = "1"; };
            if (pi.CoverStatus.ToString() != "OK") { coverstatus = "1"; };
            if (pi.FilterStatus.ToString() != "OK") { filterstatus = "1"; };

            string prtg = "<prtg>\n" +
                "<result><channel>PowerStatus</channel><value>" + powerstatus.ToString() + "</value></result>\n" + 
                "<result><channel>LampHours</channel><value>" + pi.LampHours.ToString() + "</value></result>\n" +
                "<result><channel>FanStatus</channel><value>" + fanstatus.ToString() + "</value></result>\n" +
                "<result><channel>LampStatus</channel><value>" + lampstatus.ToString() + "</value></result>\n" +
                "<result><channel>CoverStatus</channel><value>" + coverstatus.ToString() + "</value></result>\n" +
                "<result><channel>FilterStatus</channel><value>" + filterstatus.ToString() + "</value></result>\n" +
                "</prtg>";

            Console.WriteLine(prtg);

            //Console.ReadKey(); // this is only for debug to pause the output window / stop closing it
        }
    }
}

using System;

using rv;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

PJLinkConnection c = new PJLinkConnection(args[0], "rv");

if (args.Length != 1)

{

Console.WriteLine("Wrong arguments detected:");

foreach(string arg in args)

{

Console.WriteLine(arg);

}

ProjectorInfo pi = ProjectorInfo.create(c);

string powerstatus = "0";

string fanstatus = "0";

string lampstatus = "0";

string coverstatus = "0";

string filterstatus = "0";

//PowerStatus:

//OFF 0

//ON 1

//COOLING 2

//WARMUP 3

//UNKNOWN 4

if (pi.PowerStatus.ToString() == "OFF") { powerstatus = "0"; };

if (pi.PowerStatus.ToString() == "ON") { powerstatus = "1"; };

if (pi.PowerStatus.ToString() == "COOLING") { powerstatus = "2"; };

if (pi.PowerStatus.ToString() == "WARMUP") { powerstatus = "3"; };

if (pi.PowerStatus.ToString() == "UNKNOWN") { powerstatus = "4"; };

if (pi.FanStatus.ToString() != "OK") { fanstatus = "1"; };

if (pi.LampStatus.ToString() != "OK") { lampstatus = "1"; };

if (pi.CoverStatus.ToString() != "OK") { coverstatus = "1"; };

if (pi.FilterStatus.ToString() != "OK") { filterstatus = "1"; };

string prtg = "<prtg>\n" +

"<result><channel>PowerStatus</channel><value>" + powerstatus.ToString() + "</value></result>\n" +

"<result><channel>LampHours</channel><value>" + pi.LampHours.ToString() + "</value></result>\n" +

"<result><channel>FanStatus</channel><value>" + fanstatus.ToString() + "</value></result>\n" +

"<result><channel>LampStatus</channel><value>" + lampstatus.ToString() + "</value></result>\n" +

"<result><channel>CoverStatus</channel><value>" + coverstatus.ToString() + "</value></result>\n" +

"<result><channel>FilterStatus</channel><value>" + filterstatus.ToString() + "</value></result>\n" +

"</prtg>";

Console.WriteLine(prtg);

//Console.ReadKey(); // this is only for debug to pause the output window / stop closing it

}

June 1, 2018 by Florian Rossmark monitoring prtg recommendations scripts

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

Using PowerShell for Text-to-Speech

PowerShell can be used for TTS / Text-to-Speech. In this specific example, PowerShell will be used for Paessler/PRTG (www.paessler.com) text-to-speech notifications. It will actually run against a remote-system in this scenario in a central NOC/Network Operations Center room and announce down sensors/systems. This was originally posted by myself on https://kb.paessler.com/en/topic/79674-can-we-have-the-ability-to-set-audio-notifications-when-sensors-go-down-up.

You simply create the script in the path C:\Program Files (x86)\PRTG Network Monitor\Notifications\EXE and create a new notification for it.

The parameters should be configured like this:

-TargetComputer ‘COMPUTER123’ -Device ‘%device’ -Name ‘%name’ -Status ‘%status’ -Message ‘%message’

Replace the COMPUTER123 with what ever client should play the sound – in our case this is the workstation that shows the MAPs on a TV and the sound actually comes out of the TV.

You might need to enable remote power shell execution on the target system, a hint for this is the following command: Enable-PSRemoting -Force

Here is the script file: Name: PRTGtoWorkstationText2Speach.ps1

#By Florian Rossmark
Param(
	[string]$TargetComputer,
    [string]$Device,
    [string]$Name,
    [string]$Status,
    [string]$Message
)
$TextMessage = "$($Device) $($Name) is $($Status) $($Message)"
Invoke-Command -ComputerName $TargetComputer -ScriptBlock {
	$TextMessage = $args[0]
	Add-Type -AssemblyName System.speech
	$speak = New-Object System.Speech.Synthesis.SpeechSynthesizer
	$speak.Rate = -2
	$speak.Speak($TextMessage)
} -ArgumentList $TextMessage

#By Florian Rossmark

Param(

[string]$TargetComputer,

[string]$Device,

[string]$Name,

[string]$Status,

[string]$Message

)

$TextMessage = "$($Device) $($Name) is $($Status) $($Message)"

Invoke-Command -ComputerName $TargetComputer -ScriptBlock {

$TextMessage = $args[0]

Add-Type -AssemblyName System.speech

$speak = New-Object System.Speech.Synthesis.SpeechSynthesizer

$speak.Rate = -2

$speak.Speak($TextMessage)

} -ArgumentList $TextMessage

June 1, 2018 by Florian Rossmark monitoring prtg scripts solutions windows

monitoring

Monitor user accounts in Active Directory with PRTG

Monitor DFS replication backlog between servers in PRTG

Monitoring Shadow-Copies with PRTG

PRTG – Veritas Backup Exec monitoring

Advanced PRTG Slack notifitications

Monitor your projectors and light bulbs with PJLink

SQL Database backup monitoring

Using PowerShell for Text-to-Speech

Recent blog posts

Blog Archives

monitoring

Recent blog posts

Blog Archives

Tags