Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Need help with Alert count report

I have an alert where Solarwinds finds an application issue (exceeding a RAM counter using the Process Monitor function) and I've been asked provide a report of how many times that has occurred against multiple servers.

Any help is greatly appreciated.

Find more posts tagged with

Accepted answers

All comments

patriot

I would be interested in knowing how to do this as well.

jiri.tomek

Hello,
this task won't be easy to accomplish because alerts have no explicit "counter" with their trigger count but there are some options how to do what you need.

Option 1)

If you don't have alert for your issue, create it.
Open Orion database and look into "AlertDefinitions" table. Write down "AlertDefID" for your alert.
Open Report Writer (Start->All programs->SolarWinds Orion->Alerting, Reporting... -> Report Writer)
Create a new report using "Advanced SQL" type.

As SQL query for your report use:

SELECT a.Name, 	COUNT(alert.MsgID) AS NumFailuresFROM AlertLog alert JOIN APM_Application a   ON a.ID = alert.ObjectIDWHERE DATEPART(month, alert.[LogDateTime]) = DATEPART(month, GETDATE())  AND [Message] = 'Alert Triggered'  AND [AlertDefID] = 'YOUR_ALERT_DEFINITION_ID'GROUP BY a.Name

For YOUR_ALERT_DEFINITION_ID enter previously noted alert definition ID from step 2.
Specify remaining values for your report (name, filed formatting, ...)
Now you should have report for this month's failure count. If you want different time span, just change condition in WHERE clause as you need.

Option 2)

Create simple custom table in your Orion database and call it for example "ApplicationFailures". This table should have just ApplicationID(int) and DateTime(datetime) columns.
```
CREATE TABLE [dbo].[ApplicationFailures](  [ApplicationID] [int] NOT NULL,  [DateTime] [datetime] NOT NULL)
```

Create simple windows batch file and call it "create_alert_record.bat". Into this file put:

sqlcmd -S YOUR_SQL_SERVER\SQL_INSTANCE -U username -P password -d your_database_name -Q "INSERT INTO ApplicationFailures VALUES (%1,  GETDATE())"

If you already have alert set on exceeding RAM problem, just go to "Trigger Actions" tab, add new action of "Execute external program" and as program to execute specify your batch file with SQL query (for example: "C:\Scripts\create_alert_record.bat ${ID}". Note that ${ID} as argument for batch file. This will be replaced with actual application ID each time.
Now each time alert is triggered it should create one record in ApplicationFailures table.
Open Report Writer (Start->All programs->SolarWinds Orion->Alerting, Reporting... -> Report Writer)
Create a new report using "Advanced SQL" type.

As SQL query for your report use:

SELECT a.Name,    COUNT(f.ApplicationID) AS NumFailuresFROM ApplicationFailures f JOIN APM_Application a  ON a.ID = f.ApplicationIDWHERE DATEPART(month, f.[DateTime]) = DATEPART(month, GETDATE())GROUP BY a.Name

Specify remaining values for your report (name, filed formatting, ...)
Now you should have report for this month's failure count. If you want different time span, just change condition in WHERE clause as you need.

Option 3) (most complicated)

Open Report Writer (Start->All programs->SolarWinds Orion->Alerting, Reporting... -> Report Writer)
Create a new report using "Advanced SQL" type.
Here comes difficult part. Now you have to put in some SQL that gives you what you need. It should be something that counts how many times specific application changed it's status from Up to Down/Warning/Critical. This could be done for last 7 days (by default) but older statistics are aggregated to hourly data so you lose specific counts then. You could extend data retention period for detailed data to for example 30 days and then you would be able report on last months statistics.

I would try options in order as written here. The easiest is option 1. If for any reason it won't work as expected, you can try option 2, but it requires some database manipulation and you should be very careful here. Last option is really not trivial but could work as well with some modifications.

Let us know if you have any success with your task.