sql select in alert message body

[irijiyi]

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?

[ecklerwr1]

Nice SQL tricks LV :^}

[gokmen.cakmak]

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

[irijiyi]

count it is too easy - i want list

[RichardLetts]

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.

[irijiyi]

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"?

[LadaVarga]

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

[irijiyi]

it helps!

[irijiyi]

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

[LadaVarga · Accepted 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

[irijiyi]

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

[karedia]

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