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

Dupliicate records in report on website.

I have a report to list all of my enabled alerts in a report on our solarwinds web portal. I created the report in report writer and it works perfectly. However, when I run the report via the web portal, I get duplicate rows. I have tried to run the report with a "SELECT DISTINCT" however it errors because I'm pulling from multiple tables. I've also tried to use a left join without any success.

Here is my SQL statement. Anyone have any ideas?

SELECT AlertDefinitions.AlertName as "Alert Name", AlertDefinitions.AlertDescription as "Alert Description", AlertDefinitions.ObjectType, AlertDefinitions.ExecuteInterval as "Run Freqency", AlertDefinitions.TriggerSustained as "Trigger Delay", ActionDefinitions.Target
From AlertDefinitions, ActionDefinitions
WHERE AlertDefinitions.AlertDefID = ActionDefinitions.AlertDefID and AlertDefinitions.Enabled = 1
Order by AlertName

Find more posts tagged with

orion_website_customization

Database

Accepted answers

vhcato

Since the trigger and the rearm are actually noted separately in the database, you may be seeing both in your output. What happens if you add "and ActionDefinitions.TriggerAction = 1" to your WHERE statement? This will only show the entries associated with the trigger action. Also, you said you only have one target configured. Do you also have other actions configured (besides email) such as adding an entry to the event log? If so, you might also want to add "and ActionDefinitions.ActionType = 'Email' ". Something to consider... ~ Vic

All comments

mark_wiggans

The query is joining the
AlertDefinitions table to the ActionDefinitions table. The problem is that an
alert can have more than one action. So, with this query, every alert is going
to be listed once for each action defined for that alert. Except for the last
column (which is based on the action), these rows will look like
duplicates.

If I have a alert (AlertA) and it
has 3 Actions (Target1, Target2, Target3), what should be returned? The way the
query is now we’ll get:

AlertA,
Target1

AlertA,
Target2

AlertA,
Target3

The simplest fix is to not include the target
column so that you don’t have to do the join-

HTH

jkennedy

Thanks for the help. However, I only have one target configured for each alert and I'm getting multiple results with the exact same data. I need to keep the target field on my report. It's interesting since I do not get the duplicates when I preview the report in the report writer. The duplicates only show up on the web console.

vhcato

jkennedy

Thanks Vic,

Adding the "ActionDefinitions.TriggerAction = 1" to the where statement resolved my issue.

John