Email alert action for custom table poller current status

DN

Hi Fellow Members,

I trying to add Email alert action for custom table poller current status alert.

I have OID to get the status of thin AP from WLC and I have another OID to get the thin AP names.

I created alert using thin AP status OID.

if current numvalue = 3 then alert will trigger

in the alert Email i need to merge the results of status OID and Name OID for the APs which met the condition.

Both values are in the same table "custompollerstatustable" but in different column, status oid output in "numvalue "column and name oid output in "value" column,  both the results have same rowid. We can group them using this rowid column. 

I created a query to merge both the columns using rowid but  in Email alert I need only the affected AP name and status oid output.

Can someone help me with SQL query.

@rschroeder  @aLTeReGo  @mesverrum  @wluther 

Cheers,

DN

ioan_bucsa

As a personal recommendation, select the AP name as the label for the status Custom Poller (Label is also stored in CustomPollerStatusTable).

Then you select Custom SQL Alert and Custom Node Table Poller in the trigger condition. 
The SQL query is :

SELECT CustomPollerStatusTable.FullLabel, CustomPollerStatusTable.CustomPollerAssignmentID, CustomPollerStatusTable.CompressedRowID FROM CustomPollerStatusTable
INNER JOIN CustomPollerAssignmentView ON CustomPollerStatusTable.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID   
WHERE  CustomPollerAssignmentView.CustomPollerName ='<Status_Custom_Poller_Name>' 
AND CustomPollerStatusTable.NumValue=3​

Then in the alert message (or email body) you insert Custom SQL variables:

${N=SQL;M=SELECT NumValue FROM CustomPollerStatusTable WHERE CustomPollerAssignmentID='${N=SwisEntity;M=CustomPollerAssignmentID}' AND RowId='${N=SwisEntity;M=RowID}'}

${N=SQL;M=SELECT Label FROM CustomPollerStatusTable WHERE CustomPollerAssignmentID='${N=SwisEntity;M=CustomPollerAssignmentID}' AND RowId='${N=SwisEntity;M=RowID}'}