SQL Error database Monitoring

KMStolten 34
Viewed 0

Hi all

I'm trying to create a monitor for a custom SQL Error logging database but I'm facing some difficulties.

We have a SQL database that is used for central logging of errors from different applications. The database contains a table with 5 collumns (ID, Title, Exeption, Environment and Processed).

Whenever an error occurs in an application - the application writes a record to the database.

I have no problem connectiing to the database and getting the records. But I need to be able to treat every single record as 1 error-instance.

I would like the monitor to fire a separate action for each record found in the database. However I don't know how to acheive this.

Also the collumn "Processed" is used to mark the error as "handled/Solved". When a record has the "Processed" flag - the monitor should return "Fixed" for this record.

I would really like som guidance on this.

PS. I'm using PowerShell scripting in the script - however any script language suggestions are much appriciated.

Kind regards Karsten

sql scriptaction database-monitoring
6 Answers

Hello!

A single script execution has only the ability to fireactions or not fireactions. I can see how this could be a problem if you have multiple errors in the database you are reading from and you want an email for each. If the script is not running on a satellite you could use the built in email function as seen here: https://www.poweradmin.com/help/sm_6_0/monitor_execute_script.aspx#powershell and just create your own email alert function to call for each record in the table. If not that, you could just have your script only process the top 1 record each time it runs. This could cause the email alerts to be a little bit behind depending on how often the script runs.

-Matt

Ash0r13

Hi Matt

Thank you for the suggestions. I can see what you mean and it would - as I requested - result in a "Fire Action" for every error-record...

Only thing about it is that this would not handle a separate "Success/Fixed" action for each record.

I'm thinking that if it was possible to fire an alert with some kind of a Unique ID (linked to that specific record) and keep that ID for reference - and in the event of the error/event ID being removed/marked as solved - it should fire a "Success/Fixed".

This is a bit complicated - I know... And I'm not sure if it is at all solvable.

I will digg further into this and think of possible ways to solve this - but maybe this is an idea for a new feature - so that one can monitor a error-log database or other kinds of databases and create actions/alerts on a per-record basis.

Kind regards Karsten

KMStolten34

Here is what I would do...

  1. Query the database for the open/non-resolved IDs. (Current IDs)

  2. Iterate the list of previous IDs against the current ID list to find IDs that are now resolved or new.

  3. Take action depending on if resolved or new or still open.

  4. Store Current IDs as a comma separated list using the get/store value function to be used as the previous IDs the next time the script is ran: https://www.poweradmin.com/help/sm_6_0/monitor_execute_script.aspx#powershell

Thats quite a fun script you have :)

Good Luck!

-Matt

Ash0r13

Hi Matt

Thank you very much! That sounds doable! Thank you for leading the way...

I will go ahead and try it out and eventually post a reply here when it has been tested!

Kind regards Karsten

KMStolten34

Hi Matt

A work in Progress... I'm facing some challanges. Can't figure out what's going on.

Here is a copy of my script so far:

$Res = Exec-Sproc -Conn $SqlConnection -Sproc "Web.GetpoweradminServermonitorAlarms"

if ($Res -gt $null)
{

foreach ($Row in $Res)
{
    $StoredError = $mon.GetValue($Row[0])
    $mon.ReportResults()
    if ($StoredError -eq "")
    {
        $NewError="yes"
        $ErrorDetail = "`r`n" + $Row[1] + "`r`n" + $Row[2] + "`r`n" + $Row[3]
        $mon.StoreValue($Row[0],$ErrorDetail)
        $mon.Details = $ErrorDetail
        $mon.FireActions=$true
    }
    else
    {
        $NewError="no"
        $mon.Details = $StoredError
        $mon.FireActions=$true
    }
}

}

To summen up - the script runs a stored procedure that returns 2 rows/records.

Problem 1: The stored procedure is returning 2 records (Checked in Management Studion):

ID | Title | Exeption | Environment | Processed

79 a a Udvikling False
80 b b Test False

When I run the script inside the Console (Production) i get this result:

Multiple responses were received from the script. They would be processed as: Do Not Fire Actions:

Fire Actions: a a Udvikling

Fire Actions: b b Test

When I test the script for real (Let it run on a Schedule - it only returns the first record (action is send mail). And immediatly it gets "Fixed".

What am I missing?

Hope this gives meaning!

Kind regards Karsten Stolten

KMStolten34

Karsten,

I'm not sure I entirely understand the problem. Keep in mind that FireActions is set to false by default. When the script ends it checks FireActions and will activate an action if set to true, Activate the resolved action if set to false, but was previously true, or do nothing if set to false and previously set to false. Setting fireactions to true multiple times will not do anything. It is a property, not a method/function.

-Matt

Ash0r13
Related