Generate alerts based on entries from SQL Database using PS script RRS feed

  • Question

  • Hi Team,

    I created the below script to create event after reading the SQL DB, but same is not working with management pack. the same is working as separate script.

    The script debug error $output only not working, but debug info is working fine. 

    param([string]$Instance, [string]$Database, $Debug)
      if ($debug -ne "true"){$debug = [bool]$false}else{$debug = [bool]$true}
      $Script:API             = new-object -comObject "MOM.ScriptAPI"
      $Script:Bag       = $Script:API.CreatePropertyBag()
      $Script:LOG_ERROR       = 1
      $Script:LOG_WARNING     = 2
      $Script:LOG_INFORMATION = 0
      $Script:ScriptName      = "Logfile.ps1"
      $Script:Arguments       = "Received Arguments:`rInstance = $Instance`rDatabase = $Database`rDebug = $debug"
      function Write-DebugInfo([string] $msg)
          if ($debug) {
      function Write-DebugError([string] $msg)
          if ($debug) {$Script:API.LogScriptEvent("$ScriptName",2,$Script:LOG_ERROR,"`r$Arguments`r`r$msg")}
        # Check if parameter Instance and Database is provided
         if (!($Instance.Trim().Length -gt 0 -and $Database.Trim().Length -gt 0))
             Write-DebugError "ERROR: No Instance and Database specified"
    # Create and open a database connection
     Write-DebugInfo "MessageText"," Script Started"

        $sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=$Instance;database=$Database;Integrated Security=sspi"


    #Create a command object
        $sqlCommand = $sqlConnection.CreateCommand()

        $Command = "select Top 10 EventData.value('(/EventData/DataItem/Params)[1]', 'varchar(max)'),EventData.value('(/EventData/DataItem/LogFileDirectory)[1]', 'varchar(max)')
    FROM [SCOMDWCTC].[Event].[vEventDetail] evdes WITH (NOLOCK)
    inner join Event.vEvent evid on evdes.EventOriginId = evid.EventOriginId
    inner join dbo.vEventLoggingComputer evcomp on evid.LoggingComputerRowID = evcomp.EventLoggingComputerRowID
    where EventNumber = 0 and EventData.value('(/EventData/DataItem/LogFileName)[1]', 'varchar(max)') like 'E:\Application\xyz_server\Instances\xyz_instance_%'
    and DateTime > dateadd(minute,-7,getutcdate())
    Order by DateTime desc"


        $sqlCommand.CommandText = $Command
    #Execute the Command
        $sqlReader = $sqlCommand.ExecuteScalar()
        #SQL Adapter - get the results using the SQL Command
    $sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.SelectCommand = $sqlCommand
    $dataSet = new-object System.Data.Dataset
    $recordCount = $sqlAdapter.Fill($dataSet)

    # Close the database connection

        #Get single table from dataset
    $data = $dataSet.Tables[0]

    [string]$Output = $data | Select-Object @{n="Result";e={$_.Column1 + " Log File Directory -" + $_.Column2}} | Format-Table -AutoSize -Wrap -HideTableHeaders | Out-String -Stream -Width 800

    If ($Output)

    #Limit the character string for windows events 
    $result =$output.SubString(0,4000)
     Write-EventLog -LogName 'Operations Manager' -Source ApplicationRefreshxyz -EventId 1054 -Message "$result"
    Write-DebugError "$Output"
     Write-DebugInfo "MessageText"," No logfile entry found"
    Write-DebugInfo "MessageText"," Script Ended"

    Friday, May 15, 2020 2:15 PM

All replies

  • I will answer by another question : how are these event collected in the first place? if they are collected by a regular rule, it would be so much easier and cleaner to simply add a System.Health.GenerateAlert WriteAction...
    • Edited by CyrAz Friday, May 15, 2020 11:31 PM
    Friday, May 15, 2020 11:30 PM
  • Hi,

    whatr is the status here? Did the suggestion of CyrAz help? Thnaks in advance for a short status update!


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Tuesday, May 26, 2020 7:36 AM