cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

sql select in alert message body

Jump to solution

Hi

I made custom pooler for showing CDP neighbors for CISCO switches - MIB 1.3.6.1.4.1.9.9.23.1.2.1.1.6

It returns table like

Status                                   RowID

p-kazakova-acc-sw05    10123.17

p-kazakova-tunrtr01    10124.4

p-kazakova-acc-sw01    10125.12

p-kazakova-acc-sw06    10126.14

p-kazakova-acc-sw04    10127.16

name - first column and port second column

I decided to make alert when this table changes.

I made alert - this is not difficult, but i want to add this table in the alert message - and this is impossible (as i see)

Easy select like

${SQL:Select Status, RowID FROM CustomPollerStatus WHERE CustomPollerAssignmentID = '${CustomPollerAssignmentID}'  }

returns only one variable - for this query it return only p-kazakova-acc-sw05.

if i change query ${SQL:Select RowID, Select FROM CustomPollerStatus WHERE CustomPollerAssignmentID = '${CustomPollerAssignmentID}'  } - it returns 10123.17

As i suppose this is platform limitation and i can do nothing with it?

I made ten queryes, which showing first, then second, then third rows, but switches has different number of neighbors and if query fails - alert include whole sql query in the message

Has anyone problem like this? or maybe there is another way to send message with text when custom pooler has changed?

Labels (1)
1 Solution

Hello

could you please add clrf characters to string:

'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'


CHAR(13)+CHAR(10)


How to insert a line break in a SQL Server VARCHAR/NVARCHAR string - Stack Overflow

View solution in original post

0 Kudos
11 Replies

Nice SQL tricks LV :^}

0 Kudos

You are count the number of CDP neighbors. I think it's logical.

0 Kudos

count it is too easy - i want list

0 Kudos

check out these tutorials:  TSQL – Concatenate Rows using FOR XML PATH() | Sql And Me

sql server - SQL Query - Concatenating Results into One String - Stack Overflow

build the string as a nice piece of HTML in the query and get the result formatted how you like.

this is really great solution

but looks like npm don't know this syntax

i have query

SELECT

    STUFF((

        SELECT

            ', ' + Object2.Status AS [text()] , '-' + LEFT(RowID,5) AS [text()]

        FROM

            CustomPollerStatus Object2

        WHERE

            Object1.CustomPollerAssignmentID = Object2.CustomPollerAssignmentID

        FOR

            XML PATH('') ), 1, 1, '' ) AS Switch

FROM  CustomPollerStatus Object1

WHERE CustomPollerAssignmentID = '9159AD28-84CA-4EE2-888B-6A5690664FE8'

which works great in mssql management studio

but query for solarwinds

${SQL:SELECT STUFF((SELECT ', ' + Object2.Status AS [text()] , '-port' + RIGHT(LEFT(RowID,5), 2) AS [text()] FROM CustomPollerStatus Object2 WHERE Object1.CustomPollerAssignmentID = Object2.CustomPollerAssignmentID FOR XML PATH('') ), 1, 1, '' ) AS Switch FROM CustomPollerStatus Object1 WHERE CustomPollerAssignmentID = ${CustomPollerAssignmentID} AND Status not like 'SEP%'}

and it returns very strange error

MACRO SQL ERROR - Wrong syntax near construction "ad28".

Does anyboby know what is it "ad28"?

0 Kudos

Please try wrap ${CustomPollerAssignmentID} -> '${CustomPollerAssignmentID} ' as in first post.

it helps!

0 Kudos

now i have string

p-kazakova-acc-sw05-port23, p-kazakova-tunrtr01-port24, p-kazakova-acc-sw01-port25, p-kazakova-acc-sw06-port26, p-kazakova-acc-sw04-port27

in message

maybe you know how to make

p-kazakova-acc-sw05-port23,

p-kazakova-tunrtr01-port24,

p-kazakova-acc-sw01-port25,

p-kazakova-acc-sw06-port26,

p-kazakova-acc-sw04-port27

?

or

p-kazakova-acc-sw05     port23,

p-kazakova-tunrtr01     port24,

p-kazakova-acc-sw01     port25,

p-kazakova-acc-sw06     port26,

p-kazakova-acc-sw04     port27

?

npm generates text message - so html code '<br>' can't help. \n or \t also don't work

0 Kudos

Hello

could you please add clrf characters to string:

'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'


CHAR(13)+CHAR(10)


How to insert a line break in a SQL Server VARCHAR/NVARCHAR string - Stack Overflow

View solution in original post

0 Kudos

You are gorgeous

everything now fine

only CHAR(10) was enough to insert break

this is correct SQL QUERY

${SQL:SELECT STUFF((SELECT CHAR(10) + Object2.Status AS [text()] , '-port' + RIGHT(LEFT(RowID,5), 2) AS [text()] FROM CustomPollerStatus Object2 WHERE Object1.CustomPollerAssignmentID = Object2.CustomPollerAssignmentID FOR XML PATH('') ), 1, 1, '' ) AS Switch FROM CustomPollerStatus Object1 WHERE CustomPollerAssignmentID = '${CustomPollerAssignmentID}' }

now message

p-e40let-acc-sw47-port02

p-e40let-acc-sw46-port05

p-e40let-acc-sw50-port06

p-e40let-acc-sw45-port07

p-e40let-acc-sw52-port08

p-e40let-acc-sw46-port09

p-e40let-acc-sw19-port10

Hi All,

I would like to have this feature in our Solarwinds. How can I achieve this. I want to filter P2P interface that contain caption == ((P2P)).

It would be great if any one can provide step by step screen capture.

May Thanks.

Cheers,

RK

0 Kudos