Email Action Failed: ASCII local-parts (Custom Properties)

So for my alert action on emails, I'm populating the To field using a custom property associated to the object (Application, Node, Volume, etc...).  This works great as it allowed me to get a standard set of alerts in the environment that work for 99% of our situations.  This has been working great, for the most part.  The only issue I run into is when users copy/paste (possibly using the auto complete) the email action fails.

For this scenario, lets say the custom property for the object is:  Email

The email action has a To field set as:  ${N=SwisEntity;M=CustomProperties.Email}

Alert Triggers, then in the Active Alert Details, I see the following.

"Node Email Action" action has failed The client or server is only configured for E-mail addresses with ASCII local-parts: address@domain.c​om.

Note:  address@domain.com is the actual address or addresses provided in the custom property.

So my question is, I'm sure others have ran into this scenario, and I'm curious how you handle it.  I'm hoping someone has a nice way to alert on if the custom property field has an invalid character or something or knows a query that will provide a list.  Currently, I have a widget setup on my SW dashboard that gives me the list of failed alert actions, but I'd rather be a bit more proactive on the issue.  I want to say I found the base of this query in the forums somewhere, but I don't remember who to give credit to.

select aa.[timestamp] as TriggeredDateTime
    , aa.message
    ,SUBSTRING(aa.Message,CHARINDEX('"ErrorMessage","Value":"',aa.message), LENGTH(aa.message)) As ErrorMessage
    ,CASE 
        WHEN aa.eventtype = 0 then 'Triggered'
        WHEN aa.eventtype = 1 THEN 'Reset'
        WHEN aa.eventtype = 2 THEN 'Acknowledged'
        WHEN aa.eventtype = 3 THEN 'Note Added'
        WHEN aa.eventtype = 4 THEN 'Added to Incident'
        WHEN aa.eventtype = 5 THEN 'Action Failed'
        WHEN aa.eventtype = 6 THEN 'Action Succeeded'
        WHEN aa.eventtype = 7 THEN 'Unacknowledge'
        WHEN aa.eventtype = 8 THEN 'Cleared'
        END AS EventType
    ,ac.name As Alert
    ,'/Orion/NetPerfMon/ActiveALertDetails.aspx?NetObject=AAT:' + ToString(AO.AlertObjectID) AS Alert_Link
    ,ao.entityCaption as Entity
    ,ao.EntityDetailsUrl as Entity_DetailsURL
from Orion.Alerthistory aa
    join Orion.AlertObjects ao on ao.AlertObjectID = aa.AlertObjectID
    join Orion.AlertConfigurations ac on ao.AlertID = ac.AlertID
Where aa.eventtype = 5
     AND aa.[timestamp] > ADDMONTH(-1,GetDate() )
Order by  aa.[timestamp] Desc

Parents
  • Here's how I addressed this... not a common issue for us but it was enough of a nuisance that it needed an alert. Not good when you never get an alert all because some strange ASCII is in the custom property values. Generally to help prevent this from even happening I recommend anyone filling in custom properties NOT copy/paste from any other sources (like outlook, email, websites etc.). That goes a long way to preventing this issue in the first place.

    That said -- here's what I put in place, definitely test this out and validate it works for you in your environment.

    Create a SQL Function to query for the invalid ASCII:

    -- Create a SQL Function called [Find_Invalid_Chars] on the [SolarWindsOrion] Database
    
    CREATE FUNCTION [SolarWindsOrion].[dbo].[Find_Invalid_Chars]
    (
       @SearchString VARCHAR(8000)
    )
    RETURNS VARCHAR(8000)
    AS
    BEGIN
       DECLARE @CharPosition INT, @CharVal VARCHAR(100), @ReturnVal VARCHAR(8000) = '', @isValid BIT = 1 -- Start as valid
       SET @CharPosition = 1
       WHILE @CharPosition <= DATALENGTH(@SearchString)
       BEGIN
       IF (( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) NOT BETWEEN (44) AND (46) ) AND ( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) NOT BETWEEN (48) AND (57) ) AND ( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) NOT BETWEEN (64) AND (90) ) AND ( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) != 95 ) AND ( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) NOT BETWEEN (97) AND (122) ))
           BEGIN
               SET @CharVal = '[' + CAST(ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) AS VARCHAR(3)) + ']'
               SET @isValid = 0
               ---- Non-Printables
               --SET @CharVal = REPLACE(@CharVal, '[0]', '[NUL]')
               --SET @CharVal = REPLACE(@CharVal, '[1]', '[SOH]')
               --SET @CharVal = REPLACE(@CharVal, '[2]', '[STX]')
               --SET @CharVal = REPLACE(@CharVal, '[3]', '[ETX]')
               --SET @CharVal = REPLACE(@CharVal, '[4]', '[EOT]')
               --SET @CharVal = REPLACE(@CharVal, '[5]', '[ENQ]')
               --SET @CharVal = REPLACE(@CharVal, '[6]', '[ACK]')
               --SET @CharVal = REPLACE(@CharVal, '[7]', '[BEL]')
               --SET @CharVal = REPLACE(@CharVal, '[8]', '[BS]')
               --SET @CharVal = REPLACE(@CharVal, '[9]', '[HT]')
               --SET @CharVal = REPLACE(@CharVal, '[10]', '[LF]')
               --SET @CharVal = REPLACE(@CharVal, '[11]', '[VT]')
               --SET @CharVal = REPLACE(@CharVal, '[12]', '[FF]')
               --SET @CharVal = REPLACE(@CharVal, '[13]', '[CR]')
               --SET @CharVal = REPLACE(@CharVal, '[14]', '[SO]')
               --SET @CharVal = REPLACE(@CharVal, '[15]', '[SI]')
               --SET @CharVal = REPLACE(@CharVal, '[16]', '[DLE]')
               --SET @CharVal = REPLACE(@CharVal, '[17]', '[DC1]')
               --SET @CharVal = REPLACE(@CharVal, '[18]', '[DC2]')
               --SET @CharVal = REPLACE(@CharVal, '[19]', '[DC3]')
               --SET @CharVal = REPLACE(@CharVal, '[20]', '[DC4]')
               --SET @CharVal = REPLACE(@CharVal, '[21]', '[NAK]')
               --SET @CharVal = REPLACE(@CharVal, '[22]', '[SYN]')
               --SET @CharVal = REPLACE(@CharVal, '[23]', '[ETB]')
               --SET @CharVal = REPLACE(@CharVal, '[24]', '[CAN]')
               --SET @CharVal = REPLACE(@CharVal, '[25]', '[EM]')
               --SET @CharVal = REPLACE(@CharVal, '[26]', '[SUB]')
               --SET @CharVal = REPLACE(@CharVal, '[27]', '[ESC]')
               --SET @CharVal = REPLACE(@CharVal, '[28]', '[FS]')
               --SET @CharVal = REPLACE(@CharVal, '[29]', '[GS]')
               --SET @CharVal = REPLACE(@CharVal, '[30]', '[RS]')
               --SET @CharVal = REPLACE(@CharVal, '[31]', '[US]')
               ---- Extended Ascii
               --SET @CharVal = REPLACE(@CharVal, '[128]', '[€]')
               --SET @CharVal = REPLACE(@CharVal, '[129]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[130]', '[‚]')
               --SET @CharVal = REPLACE(@CharVal, '[131]', '[ƒ]')
               --SET @CharVal = REPLACE(@CharVal, '[132]', '[„]')
               --SET @CharVal = REPLACE(@CharVal, '[133]', '[…]')
               --SET @CharVal = REPLACE(@CharVal, '[134]', '[†]')
               --SET @CharVal = REPLACE(@CharVal, '[135]', '[‡]')
               --SET @CharVal = REPLACE(@CharVal, '[136]', '[ˆ]')
               --SET @CharVal = REPLACE(@CharVal, '[137]', '[‰]')
               --SET @CharVal = REPLACE(@CharVal, '[138]', '[Š]')
               --SET @CharVal = REPLACE(@CharVal, '[139]', '[‹]')
               --SET @CharVal = REPLACE(@CharVal, '[140]', '[Œ]')
               --SET @CharVal = REPLACE(@CharVal, '[141]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[142]', '[Ž]')
               --SET @CharVal = REPLACE(@CharVal, '[143]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[144]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[145]', '[‘]')
               --SET @CharVal = REPLACE(@CharVal, '[146]', '[’]')
               --SET @CharVal = REPLACE(@CharVal, '[147]', '[“]')
               --SET @CharVal = REPLACE(@CharVal, '[148]', '[”]')
               --SET @CharVal = REPLACE(@CharVal, '[149]', '[•]')
               --SET @CharVal = REPLACE(@CharVal, '[150]', '[–]')
               --SET @CharVal = REPLACE(@CharVal, '[151]', '[—]')
               --SET @CharVal = REPLACE(@CharVal, '[152]', '[˜]')
               --SET @CharVal = REPLACE(@CharVal, '[153]', '[™]')
               --SET @CharVal = REPLACE(@CharVal, '[154]', '[š]')
               --SET @CharVal = REPLACE(@CharVal, '[155]', '[›]')
               --SET @CharVal = REPLACE(@CharVal, '[156]', '[œ]')
               --SET @CharVal = REPLACE(@CharVal, '[157]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[158]', '[ž]')
               --SET @CharVal = REPLACE(@CharVal, '[159]', '[Ÿ]')
               --SET @CharVal = REPLACE(@CharVal, '[160]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[161]', '[¡]')
               --SET @CharVal = REPLACE(@CharVal, '[162]', '[¢]')
               --SET @CharVal = REPLACE(@CharVal, '[163]', '[£]')
               --SET @CharVal = REPLACE(@CharVal, '[164]', '[¤]')
               --SET @CharVal = REPLACE(@CharVal, '[165]', '[¥]')
               --SET @CharVal = REPLACE(@CharVal, '[166]', '[¦]')
               --SET @CharVal = REPLACE(@CharVal, '[167]', '[§]')
               --SET @CharVal = REPLACE(@CharVal, '[168]', '[¨]')
               --SET @CharVal = REPLACE(@CharVal, '[169]', '[©]')
               --SET @CharVal = REPLACE(@CharVal, '[170]', '[ª]')
               --SET @CharVal = REPLACE(@CharVal, '[171]', '[«]')
               --SET @CharVal = REPLACE(@CharVal, '[172]', '[¬]')
               --SET @CharVal = REPLACE(@CharVal, '[173]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[174]', '[®]')
               --SET @CharVal = REPLACE(@CharVal, '[175]', '[¯]')
               --SET @CharVal = REPLACE(@CharVal, '[176]', '[°]')
               --SET @CharVal = REPLACE(@CharVal, '[177]', '[±]')
               --SET @CharVal = REPLACE(@CharVal, '[178]', '[²]')
               --SET @CharVal = REPLACE(@CharVal, '[179]', '[³]')
               --SET @CharVal = REPLACE(@CharVal, '[180]', '[´]')
               --SET @CharVal = REPLACE(@CharVal, '[181]', '[µ]')
               --SET @CharVal = REPLACE(@CharVal, '[182]', '[¶]')
               --SET @CharVal = REPLACE(@CharVal, '[183]', '[·]')
               --SET @CharVal = REPLACE(@CharVal, '[184]', '[¸]')
               --SET @CharVal = REPLACE(@CharVal, '[185]', '[¹]')
               --SET @CharVal = REPLACE(@CharVal, '[186]', '[º]')
               --SET @CharVal = REPLACE(@CharVal, '[187]', '[»]')
               --SET @CharVal = REPLACE(@CharVal, '[188]', '[¼]')
               --SET @CharVal = REPLACE(@CharVal, '[189]', '[½]')
               --SET @CharVal = REPLACE(@CharVal, '[190]', '[¾]')
               --SET @CharVal = REPLACE(@CharVal, '[191]', '[¿]')
               --SET @CharVal = REPLACE(@CharVal, '[192]', '[À]')
               --SET @CharVal = REPLACE(@CharVal, '[193]', '[Á]')
               --SET @CharVal = REPLACE(@CharVal, '[194]', '[Â]')
               --SET @CharVal = REPLACE(@CharVal, '[195]', '[Ã]')
               --SET @CharVal = REPLACE(@CharVal, '[196]', '[Ä]')
               --SET @CharVal = REPLACE(@CharVal, '[197]', '[Å]')
               --SET @CharVal = REPLACE(@CharVal, '[198]', '[Æ]')
               --SET @CharVal = REPLACE(@CharVal, '[199]', '[Ç]')
               --SET @CharVal = REPLACE(@CharVal, '[200]', '[È]')
               --SET @CharVal = REPLACE(@CharVal, '[201]', '[É]')
               --SET @CharVal = REPLACE(@CharVal, '[202]', '[Ê]')
               --SET @CharVal = REPLACE(@CharVal, '[203]', '[Ë]')
               --SET @CharVal = REPLACE(@CharVal, '[204]', '[Ì]')
               --SET @CharVal = REPLACE(@CharVal, '[205]', '[Í]')
               --SET @CharVal = REPLACE(@CharVal, '[206]', '[Î]')
               --SET @CharVal = REPLACE(@CharVal, '[207]', '[Ï]')
               --SET @CharVal = REPLACE(@CharVal, '[208]', '[Ð]')
               --SET @CharVal = REPLACE(@CharVal, '[209]', '[Ñ]')
               --SET @CharVal = REPLACE(@CharVal, '[210]', '[Ò]')
               --SET @CharVal = REPLACE(@CharVal, '[211]', '[Ó]')
               --SET @CharVal = REPLACE(@CharVal, '[212]', '[Ô]')
               --SET @CharVal = REPLACE(@CharVal, '[213]', '[Õ]')
               --SET @CharVal = REPLACE(@CharVal, '[214]', '[Ö]')
               --SET @CharVal = REPLACE(@CharVal, '[215]', '[×]')
               --SET @CharVal = REPLACE(@CharVal, '[216]', '[Ø]')
               --SET @CharVal = REPLACE(@CharVal, '[217]', '[Ù]')
               --SET @CharVal = REPLACE(@CharVal, '[218]', '[Ú]')
               --SET @CharVal = REPLACE(@CharVal, '[219]', '[Û]')
               --SET @CharVal = REPLACE(@CharVal, '[220]', '[Ü]')
               --SET @CharVal = REPLACE(@CharVal, '[221]', '[Ý]')
               --SET @CharVal = REPLACE(@CharVal, '[222]', '[Þ]')
               --SET @CharVal = REPLACE(@CharVal, '[223]', '[ß]')
               --SET @CharVal = REPLACE(@CharVal, '[224]', '[à]')
               --SET @CharVal = REPLACE(@CharVal, '[225]', '[á]')
               --SET @CharVal = REPLACE(@CharVal, '[226]', '[â]')
               --SET @CharVal = REPLACE(@CharVal, '[227]', '[ã]')
               --SET @CharVal = REPLACE(@CharVal, '[228]', '[ä]')
               --SET @CharVal = REPLACE(@CharVal, '[229]', '[å]')
               --SET @CharVal = REPLACE(@CharVal, '[230]', '[æ]')
               --SET @CharVal = REPLACE(@CharVal, '[231]', '[ç]')
               --SET @CharVal = REPLACE(@CharVal, '[232]', '[è]')
               --SET @CharVal = REPLACE(@CharVal, '[233]', '[é]')
               --SET @CharVal = REPLACE(@CharVal, '[234]', '[ê]')
               --SET @CharVal = REPLACE(@CharVal, '[235]', '[ë]')
               --SET @CharVal = REPLACE(@CharVal, '[236]', '[ì]')
               --SET @CharVal = REPLACE(@CharVal, '[237]', '[í]')
               --SET @CharVal = REPLACE(@CharVal, '[238]', '[î]')
               --SET @CharVal = REPLACE(@CharVal, '[239]', '[ï]')
               --SET @CharVal = REPLACE(@CharVal, '[240]', '[ð]')
               --SET @CharVal = REPLACE(@CharVal, '[241]', '[ñ]')
               --SET @CharVal = REPLACE(@CharVal, '[242]', '[ò]')
               --SET @CharVal = REPLACE(@CharVal, '[243]', '[ó]')
               --SET @CharVal = REPLACE(@CharVal, '[244]', '[ô]')
               --SET @CharVal = REPLACE(@CharVal, '[245]', '[õ]')
               --SET @CharVal = REPLACE(@CharVal, '[246]', '[ö]')
               --SET @CharVal = REPLACE(@CharVal, '[247]', '[÷]')
               --SET @CharVal = REPLACE(@CharVal, '[248]', '[ø]')
               --SET @CharVal = REPLACE(@CharVal, '[249]', '[ù]')
               --SET @CharVal = REPLACE(@CharVal, '[250]', '[ú]')
               --SET @CharVal = REPLACE(@CharVal, '[251]', '[û]')
               --SET @CharVal = REPLACE(@CharVal, '[252]', '[ü]')
               --SET @CharVal = REPLACE(@CharVal, '[253]', '[ý]')
               --SET @CharVal = REPLACE(@CharVal, '[254]', '[þ]')
               --SET @CharVal = REPLACE(@CharVal, '[255]', '[ÿ]')
           END
       ELSE
           BEGIN
               SET @CharVal = SUBSTRING(@SearchString, @CharPosition, 1)
           END
       SET @ReturnVal = @ReturnVal + @CharVal
       SET @CharPosition = @CharPosition + 1
       END
    
    IF (@isValid = 1) SET @ReturnVal = NULL
    
    RETURN @ReturnVal
    
    END

    Then create a custom SQL alert with the following condition:

    ## This top SELECT line is provided automatically by the custom SQL alert
    ##
    ## SELECT Nodes.Caption, Nodes.NodeID FROM Nodes
    
    INNER JOIN ( SELECT n.NodeID, [SolarWindsOrion].[dbo].Find_Invalid_Chars(np.emailProp) AS [Invalid Characters1]
    FROM [SolarWindsOrion].[dbo].[NodesData] AS n
    INNER JOIN [SolarWindsOrion].[dbo].[NodesCustomProperties] AS np ON np.NodeID = n.NodeID ) AS sub1 ON sub1.NodeID = Nodes.NodeID
    WHERE ( sub1.[Invalid Characters1] IS NOT NULL )

    Then assign an action that sends an email to you and/or appropriate SolarWinds Admins.

    Hope this helps, and good luck!

Reply
  • Here's how I addressed this... not a common issue for us but it was enough of a nuisance that it needed an alert. Not good when you never get an alert all because some strange ASCII is in the custom property values. Generally to help prevent this from even happening I recommend anyone filling in custom properties NOT copy/paste from any other sources (like outlook, email, websites etc.). That goes a long way to preventing this issue in the first place.

    That said -- here's what I put in place, definitely test this out and validate it works for you in your environment.

    Create a SQL Function to query for the invalid ASCII:

    -- Create a SQL Function called [Find_Invalid_Chars] on the [SolarWindsOrion] Database
    
    CREATE FUNCTION [SolarWindsOrion].[dbo].[Find_Invalid_Chars]
    (
       @SearchString VARCHAR(8000)
    )
    RETURNS VARCHAR(8000)
    AS
    BEGIN
       DECLARE @CharPosition INT, @CharVal VARCHAR(100), @ReturnVal VARCHAR(8000) = '', @isValid BIT = 1 -- Start as valid
       SET @CharPosition = 1
       WHILE @CharPosition <= DATALENGTH(@SearchString)
       BEGIN
       IF (( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) NOT BETWEEN (44) AND (46) ) AND ( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) NOT BETWEEN (48) AND (57) ) AND ( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) NOT BETWEEN (64) AND (90) ) AND ( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) != 95 ) AND ( ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) NOT BETWEEN (97) AND (122) ))
           BEGIN
               SET @CharVal = '[' + CAST(ASCII(SUBSTRING(@SearchString, @CharPosition, 1)) AS VARCHAR(3)) + ']'
               SET @isValid = 0
               ---- Non-Printables
               --SET @CharVal = REPLACE(@CharVal, '[0]', '[NUL]')
               --SET @CharVal = REPLACE(@CharVal, '[1]', '[SOH]')
               --SET @CharVal = REPLACE(@CharVal, '[2]', '[STX]')
               --SET @CharVal = REPLACE(@CharVal, '[3]', '[ETX]')
               --SET @CharVal = REPLACE(@CharVal, '[4]', '[EOT]')
               --SET @CharVal = REPLACE(@CharVal, '[5]', '[ENQ]')
               --SET @CharVal = REPLACE(@CharVal, '[6]', '[ACK]')
               --SET @CharVal = REPLACE(@CharVal, '[7]', '[BEL]')
               --SET @CharVal = REPLACE(@CharVal, '[8]', '[BS]')
               --SET @CharVal = REPLACE(@CharVal, '[9]', '[HT]')
               --SET @CharVal = REPLACE(@CharVal, '[10]', '[LF]')
               --SET @CharVal = REPLACE(@CharVal, '[11]', '[VT]')
               --SET @CharVal = REPLACE(@CharVal, '[12]', '[FF]')
               --SET @CharVal = REPLACE(@CharVal, '[13]', '[CR]')
               --SET @CharVal = REPLACE(@CharVal, '[14]', '[SO]')
               --SET @CharVal = REPLACE(@CharVal, '[15]', '[SI]')
               --SET @CharVal = REPLACE(@CharVal, '[16]', '[DLE]')
               --SET @CharVal = REPLACE(@CharVal, '[17]', '[DC1]')
               --SET @CharVal = REPLACE(@CharVal, '[18]', '[DC2]')
               --SET @CharVal = REPLACE(@CharVal, '[19]', '[DC3]')
               --SET @CharVal = REPLACE(@CharVal, '[20]', '[DC4]')
               --SET @CharVal = REPLACE(@CharVal, '[21]', '[NAK]')
               --SET @CharVal = REPLACE(@CharVal, '[22]', '[SYN]')
               --SET @CharVal = REPLACE(@CharVal, '[23]', '[ETB]')
               --SET @CharVal = REPLACE(@CharVal, '[24]', '[CAN]')
               --SET @CharVal = REPLACE(@CharVal, '[25]', '[EM]')
               --SET @CharVal = REPLACE(@CharVal, '[26]', '[SUB]')
               --SET @CharVal = REPLACE(@CharVal, '[27]', '[ESC]')
               --SET @CharVal = REPLACE(@CharVal, '[28]', '[FS]')
               --SET @CharVal = REPLACE(@CharVal, '[29]', '[GS]')
               --SET @CharVal = REPLACE(@CharVal, '[30]', '[RS]')
               --SET @CharVal = REPLACE(@CharVal, '[31]', '[US]')
               ---- Extended Ascii
               --SET @CharVal = REPLACE(@CharVal, '[128]', '[€]')
               --SET @CharVal = REPLACE(@CharVal, '[129]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[130]', '[‚]')
               --SET @CharVal = REPLACE(@CharVal, '[131]', '[ƒ]')
               --SET @CharVal = REPLACE(@CharVal, '[132]', '[„]')
               --SET @CharVal = REPLACE(@CharVal, '[133]', '[…]')
               --SET @CharVal = REPLACE(@CharVal, '[134]', '[†]')
               --SET @CharVal = REPLACE(@CharVal, '[135]', '[‡]')
               --SET @CharVal = REPLACE(@CharVal, '[136]', '[ˆ]')
               --SET @CharVal = REPLACE(@CharVal, '[137]', '[‰]')
               --SET @CharVal = REPLACE(@CharVal, '[138]', '[Š]')
               --SET @CharVal = REPLACE(@CharVal, '[139]', '[‹]')
               --SET @CharVal = REPLACE(@CharVal, '[140]', '[Œ]')
               --SET @CharVal = REPLACE(@CharVal, '[141]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[142]', '[Ž]')
               --SET @CharVal = REPLACE(@CharVal, '[143]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[144]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[145]', '[‘]')
               --SET @CharVal = REPLACE(@CharVal, '[146]', '[’]')
               --SET @CharVal = REPLACE(@CharVal, '[147]', '[“]')
               --SET @CharVal = REPLACE(@CharVal, '[148]', '[”]')
               --SET @CharVal = REPLACE(@CharVal, '[149]', '[•]')
               --SET @CharVal = REPLACE(@CharVal, '[150]', '[–]')
               --SET @CharVal = REPLACE(@CharVal, '[151]', '[—]')
               --SET @CharVal = REPLACE(@CharVal, '[152]', '[˜]')
               --SET @CharVal = REPLACE(@CharVal, '[153]', '[™]')
               --SET @CharVal = REPLACE(@CharVal, '[154]', '[š]')
               --SET @CharVal = REPLACE(@CharVal, '[155]', '[›]')
               --SET @CharVal = REPLACE(@CharVal, '[156]', '[œ]')
               --SET @CharVal = REPLACE(@CharVal, '[157]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[158]', '[ž]')
               --SET @CharVal = REPLACE(@CharVal, '[159]', '[Ÿ]')
               --SET @CharVal = REPLACE(@CharVal, '[160]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[161]', '[¡]')
               --SET @CharVal = REPLACE(@CharVal, '[162]', '[¢]')
               --SET @CharVal = REPLACE(@CharVal, '[163]', '[£]')
               --SET @CharVal = REPLACE(@CharVal, '[164]', '[¤]')
               --SET @CharVal = REPLACE(@CharVal, '[165]', '[¥]')
               --SET @CharVal = REPLACE(@CharVal, '[166]', '[¦]')
               --SET @CharVal = REPLACE(@CharVal, '[167]', '[§]')
               --SET @CharVal = REPLACE(@CharVal, '[168]', '[¨]')
               --SET @CharVal = REPLACE(@CharVal, '[169]', '[©]')
               --SET @CharVal = REPLACE(@CharVal, '[170]', '[ª]')
               --SET @CharVal = REPLACE(@CharVal, '[171]', '[«]')
               --SET @CharVal = REPLACE(@CharVal, '[172]', '[¬]')
               --SET @CharVal = REPLACE(@CharVal, '[173]', '[]]')
               --SET @CharVal = REPLACE(@CharVal, '[174]', '[®]')
               --SET @CharVal = REPLACE(@CharVal, '[175]', '[¯]')
               --SET @CharVal = REPLACE(@CharVal, '[176]', '[°]')
               --SET @CharVal = REPLACE(@CharVal, '[177]', '[±]')
               --SET @CharVal = REPLACE(@CharVal, '[178]', '[²]')
               --SET @CharVal = REPLACE(@CharVal, '[179]', '[³]')
               --SET @CharVal = REPLACE(@CharVal, '[180]', '[´]')
               --SET @CharVal = REPLACE(@CharVal, '[181]', '[µ]')
               --SET @CharVal = REPLACE(@CharVal, '[182]', '[¶]')
               --SET @CharVal = REPLACE(@CharVal, '[183]', '[·]')
               --SET @CharVal = REPLACE(@CharVal, '[184]', '[¸]')
               --SET @CharVal = REPLACE(@CharVal, '[185]', '[¹]')
               --SET @CharVal = REPLACE(@CharVal, '[186]', '[º]')
               --SET @CharVal = REPLACE(@CharVal, '[187]', '[»]')
               --SET @CharVal = REPLACE(@CharVal, '[188]', '[¼]')
               --SET @CharVal = REPLACE(@CharVal, '[189]', '[½]')
               --SET @CharVal = REPLACE(@CharVal, '[190]', '[¾]')
               --SET @CharVal = REPLACE(@CharVal, '[191]', '[¿]')
               --SET @CharVal = REPLACE(@CharVal, '[192]', '[À]')
               --SET @CharVal = REPLACE(@CharVal, '[193]', '[Á]')
               --SET @CharVal = REPLACE(@CharVal, '[194]', '[Â]')
               --SET @CharVal = REPLACE(@CharVal, '[195]', '[Ã]')
               --SET @CharVal = REPLACE(@CharVal, '[196]', '[Ä]')
               --SET @CharVal = REPLACE(@CharVal, '[197]', '[Å]')
               --SET @CharVal = REPLACE(@CharVal, '[198]', '[Æ]')
               --SET @CharVal = REPLACE(@CharVal, '[199]', '[Ç]')
               --SET @CharVal = REPLACE(@CharVal, '[200]', '[È]')
               --SET @CharVal = REPLACE(@CharVal, '[201]', '[É]')
               --SET @CharVal = REPLACE(@CharVal, '[202]', '[Ê]')
               --SET @CharVal = REPLACE(@CharVal, '[203]', '[Ë]')
               --SET @CharVal = REPLACE(@CharVal, '[204]', '[Ì]')
               --SET @CharVal = REPLACE(@CharVal, '[205]', '[Í]')
               --SET @CharVal = REPLACE(@CharVal, '[206]', '[Î]')
               --SET @CharVal = REPLACE(@CharVal, '[207]', '[Ï]')
               --SET @CharVal = REPLACE(@CharVal, '[208]', '[Ð]')
               --SET @CharVal = REPLACE(@CharVal, '[209]', '[Ñ]')
               --SET @CharVal = REPLACE(@CharVal, '[210]', '[Ò]')
               --SET @CharVal = REPLACE(@CharVal, '[211]', '[Ó]')
               --SET @CharVal = REPLACE(@CharVal, '[212]', '[Ô]')
               --SET @CharVal = REPLACE(@CharVal, '[213]', '[Õ]')
               --SET @CharVal = REPLACE(@CharVal, '[214]', '[Ö]')
               --SET @CharVal = REPLACE(@CharVal, '[215]', '[×]')
               --SET @CharVal = REPLACE(@CharVal, '[216]', '[Ø]')
               --SET @CharVal = REPLACE(@CharVal, '[217]', '[Ù]')
               --SET @CharVal = REPLACE(@CharVal, '[218]', '[Ú]')
               --SET @CharVal = REPLACE(@CharVal, '[219]', '[Û]')
               --SET @CharVal = REPLACE(@CharVal, '[220]', '[Ü]')
               --SET @CharVal = REPLACE(@CharVal, '[221]', '[Ý]')
               --SET @CharVal = REPLACE(@CharVal, '[222]', '[Þ]')
               --SET @CharVal = REPLACE(@CharVal, '[223]', '[ß]')
               --SET @CharVal = REPLACE(@CharVal, '[224]', '[à]')
               --SET @CharVal = REPLACE(@CharVal, '[225]', '[á]')
               --SET @CharVal = REPLACE(@CharVal, '[226]', '[â]')
               --SET @CharVal = REPLACE(@CharVal, '[227]', '[ã]')
               --SET @CharVal = REPLACE(@CharVal, '[228]', '[ä]')
               --SET @CharVal = REPLACE(@CharVal, '[229]', '[å]')
               --SET @CharVal = REPLACE(@CharVal, '[230]', '[æ]')
               --SET @CharVal = REPLACE(@CharVal, '[231]', '[ç]')
               --SET @CharVal = REPLACE(@CharVal, '[232]', '[è]')
               --SET @CharVal = REPLACE(@CharVal, '[233]', '[é]')
               --SET @CharVal = REPLACE(@CharVal, '[234]', '[ê]')
               --SET @CharVal = REPLACE(@CharVal, '[235]', '[ë]')
               --SET @CharVal = REPLACE(@CharVal, '[236]', '[ì]')
               --SET @CharVal = REPLACE(@CharVal, '[237]', '[í]')
               --SET @CharVal = REPLACE(@CharVal, '[238]', '[î]')
               --SET @CharVal = REPLACE(@CharVal, '[239]', '[ï]')
               --SET @CharVal = REPLACE(@CharVal, '[240]', '[ð]')
               --SET @CharVal = REPLACE(@CharVal, '[241]', '[ñ]')
               --SET @CharVal = REPLACE(@CharVal, '[242]', '[ò]')
               --SET @CharVal = REPLACE(@CharVal, '[243]', '[ó]')
               --SET @CharVal = REPLACE(@CharVal, '[244]', '[ô]')
               --SET @CharVal = REPLACE(@CharVal, '[245]', '[õ]')
               --SET @CharVal = REPLACE(@CharVal, '[246]', '[ö]')
               --SET @CharVal = REPLACE(@CharVal, '[247]', '[÷]')
               --SET @CharVal = REPLACE(@CharVal, '[248]', '[ø]')
               --SET @CharVal = REPLACE(@CharVal, '[249]', '[ù]')
               --SET @CharVal = REPLACE(@CharVal, '[250]', '[ú]')
               --SET @CharVal = REPLACE(@CharVal, '[251]', '[û]')
               --SET @CharVal = REPLACE(@CharVal, '[252]', '[ü]')
               --SET @CharVal = REPLACE(@CharVal, '[253]', '[ý]')
               --SET @CharVal = REPLACE(@CharVal, '[254]', '[þ]')
               --SET @CharVal = REPLACE(@CharVal, '[255]', '[ÿ]')
           END
       ELSE
           BEGIN
               SET @CharVal = SUBSTRING(@SearchString, @CharPosition, 1)
           END
       SET @ReturnVal = @ReturnVal + @CharVal
       SET @CharPosition = @CharPosition + 1
       END
    
    IF (@isValid = 1) SET @ReturnVal = NULL
    
    RETURN @ReturnVal
    
    END

    Then create a custom SQL alert with the following condition:

    ## This top SELECT line is provided automatically by the custom SQL alert
    ##
    ## SELECT Nodes.Caption, Nodes.NodeID FROM Nodes
    
    INNER JOIN ( SELECT n.NodeID, [SolarWindsOrion].[dbo].Find_Invalid_Chars(np.emailProp) AS [Invalid Characters1]
    FROM [SolarWindsOrion].[dbo].[NodesData] AS n
    INNER JOIN [SolarWindsOrion].[dbo].[NodesCustomProperties] AS np ON np.NodeID = n.NodeID ) AS sub1 ON sub1.NodeID = Nodes.NodeID
    WHERE ( sub1.[Invalid Characters1] IS NOT NULL )

    Then assign an action that sends an email to you and/or appropriate SolarWinds Admins.

    Hope this helps, and good luck!

Children
  • So we started testing it, and its a pretty good start.

    I'm seeing som false alarms due to [59][32] which appears to be a semicolon and a space, which appear to work with the alerts.  Is there an easy way to filter those out?

    Also have you tried to throw that into a modern dashboard or anything?  I'm trying to see how to call that within SWQL.

    Thanks

  • Yes but are spaces and semicolons valid for email addresses? I don't think so. The only delimiter (when adding multiple email addresses) that SolarWinds accepts is a comma I believe.

    I don't think there's an easy way to get this into SWQL. I myself would LOVE if modern dadhboards allowed for SQL datasources.

    Anyway there certainly could be some ways... maybe as an action of that alert you set a boolean custom property value (e.g. cp_email_bad_ascii) to true. That could then be queried in SWQL and in turn put into modern dashboards..

  • I double checked and our alerts are working fine with the "Address1@domain.com; Address2@domain.com" in the custom property.  So maybe an old limitation, or could be mine are working where they shouldn't be (have to love technology).

    I'm still doing some research to see a SWQL way, and working with one of our DBAs who has a couple ideas.

  • That's interesting. Are you on the latest versions? Maybe it does work and it's all in my head Slight smile

    Anywho. There's definitely a PowerShell solution to this also which could take it out of your DBA's hands.

    Here's a rough example (untested FYI). Update the $serverName variable, your custom property that contains email addresses (cp_email_address) and I put in the $makeChanges variable as well (set to false currently) so if you want to have it run through, and show you what it WOULD change if anything...

    As a note I have the SQL function setup to essentially only allow permissible characters in email addresses and the field delimiter (which we use a comma but you could adjust for semicolon). I didn't do this for the powershell example but the ASCII codes are easy enough to depict and adjust the powershell from there.

    The same could apply, this could set a custom property to TRUE as opposed to removing the bad characters instead and then you could query that boolean custom property in SWQL / modern dashboards. I don't believe SWQL has any native way to check for bad character codes that I'm aware of.

    $serverName = 'x.x.x.x'
    
    $cred = Get-Credential
    
    $swis = Connect-Swis -Hostname $serverName -Credential $cred
    
    $query = @"
            SELECT
                     n.Caption
                    ,n.NodeID
                    ,n.Uri
                    ,n.CustomProperties.cp_email_address
            FROM Orion.Nodes AS n
            WHERE ( n.CustomProperties.cp_email_address IS NOT NULL 
    		AND n.CustomProperties.cp_email_address != '' )
    "@
    
    $selection = Get-SwisData -SwisConnection $swis -Query $query
    
    $makeChanges = $false
    
    $charMatch = '[^\x20-\x7F]'
    
    ForEach ($node in $selection) {
    
            $cpUri = $null
    
            Write-Output("Assessing node " + $node.Caption + " (" + [string] $node.NodeID + ")")
    
            If ($node.cp_email_address -cmatch $charMatch) {
    
                    Write-Output("`tUh oh, there's invalid characters in cp_email_address on this node.")
    
                    If($makeChanges) {
    
                            $cpUri = $node.Uri + "/CustomProperties"
    
                            $newCpValue = $node.cp_email_address -replace "[^ -~]"
    
                            Set-SwisObject -SwisConnection $swis -Uri $cpUri -Properties @{
    
                                    cp_email_address = $newCpValue
    
                            }
    
                            Write-Output("`tNon-printable ASCII removed from cp_email_address on this node.")
    
                    } Else {
    
                            Write-Output("`tcp_email_address has non-printable ASCII characters but " + '$charmatch' + " is set to false.")
    
                    }
    
            }
    
    }

    https://www.ascii-code.com/