solarwinds  |  thwack
in
Search 44,781 posts and 623 resources contributed by 21,552 members or post a topic.

Already Joined? Sign in
Dupliicate records in report on website.

rated by 0 users
Answered (Verified) This post has 1 verified answer | 4 Replies | 1 Follower


47 Posts
Points 115
jkennedy posted on 06-19-2007 12:29 PM
rated by 0 users

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

Answered (Verified) Verified Answer


415 Posts
Points 2,023
Answered (Verified) vhcato replied on 06-21-2007 6:18 AM
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

Vic Cato
Technical Lead - Network Operations
Koch Business Solutions - Enterprise Technical Services

  • | Post Points: 3

All Replies


2,136 Posts
Points 5,341
Moderator
SolarWinds Employee
Mark Wiggans replied on 06-20-2007 8:08 AM
rated by 0 users

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 

 

Mark Wiggans Thwack Moderator

  • | Post Points: 3

47 Posts
Points 115
jkennedy replied on 06-20-2007 11:16 AM
rated by 0 users

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.

  • | Post Points: 3

415 Posts
Points 2,023
Answered (Verified) vhcato replied on 06-21-2007 6:18 AM
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

Vic Cato
Technical Lead - Network Operations
Koch Business Solutions - Enterprise Technical Services

  • | Post Points: 3

47 Posts
Points 115
jkennedy replied on 06-21-2007 11:19 AM
rated by 0 users

Thanks Vic,

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

 John

  • | Post Points: 1
Page 1 of 1 (5 items) | RSS