database.ps1 - powershell script by ActiveXperts Software

database.ps1 checks a database by executing an SQL query.

Use database.ps1 directly from ActiveXperts Network Monitor; in the Manager's 'Monitor' menu, select 'New Check (Script)' and select database.ps1. Configure the required parameter, or press 'Load a working sample'.

In ActiveXperts Network Monitor, Administrators can use three different scripting languages: Powershell, VBScript and SSH.

---

database.ps1 script code

#################################################################################
# ActiveXperts Network Monitor PowerShell script, © ActiveXperts Software B.V.
# For more information about ActiveXperts Network Monitor, visit the ActiveXperts 
# Network Monitor web site at http://www.activexperts.com
#################################################################################
# Script
#     Database.ps1
# Description:
#     Check a database by counting the number of records. When this count is less than expected, it is 
#     considered as error
# Declare Parameters:
#     1) strConnectionString (string) - An OLE/DB connection string
#     2) strQuery (string) - SQL Query to be executed
#     3) nMinimumCount (int) - Minimum number of records required in the database table
# Usage:
#     .\Database.ps1 '<ConnectionString>' '<Query>' <nMinimumCount>
# Sample:
#     .\Database.ps1 'Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Program Files\ActiveXperts\Network Monitor\Samples\Northwind.sdf' 'SELECT * FROM Customers' 1
#################################################################################

# -- Declare Parameters
param( [string]$strConnectionString = '', [string]$strSqlQuery = '', [int]$nMinimumCount = -1 )

# -- Use _activexperts.ps1 with common functions
. 'C:\Program Files\ActiveXperts\Network Monitor\Scripts\Monitor (ps1)\_activexperts.ps1'


#################################################################################
# // --- Main script ---
#################################################################################

# -- Clear screen and clear error
cls
$Error.Clear()

# -- Validate parameters, return on parameter mismatch
if( $strConnectionString -eq '' -or $strSqlQuery -eq '' -or $nMinimumCount -lt 0 )
{
  $res = 'UNCERTAIN: Parameter error - Usage: .\Database.ps1 "<ConnectionString>" "<Query>" <nMinimumCount>'
  echo $res
  exit 
}

# -- Declare local variables by assigning initial value
$objConn = new-object -comobject ADODB.Connection
$objRecords = new-object -comobject ADODB.Recordset
$nRecordCount = 0

$objConn.Open( $strConnectionString )   

$objRecords.Open( $strSqlQuery,$objConn )

if( $objRecords.EOF -eq $true )
{
  $objConn.Close()
  $res = 'ERROR: No record returned from query [' + $strSqlQuery + ']'
  echo $res
  exit
}  

$objRecords.MoveFirst()
while( $objRecords.EOF -ne $true )
{
  $nRecordCount = $nRecordCount + 1
  $objRecords.MoveNext()
}


if( $nMinimumCount -le $nRecordCount )
{
  $res = 'SUCCESS: '
}
else
{
  $res = 'ERROR: '
}

$objConn.Close()
 
# -- Print script result 
$res += 'Database checked. #records found=[' + $nRecordCount + '], minimum required=[' + $nMinimumCount + '] DATA: ' + $nRecordCount
echo $res
exit


#################################################################################
# // --- Catch script exceptions ---
#################################################################################

trap [Exception]
{
  $res = 'ERROR: ' + $_.Exception.Message
  echo $res
  exit
}