How to format AlterTriggerTime AlertVariable to desired format

tdanner​ Is this something which you can help me with me ? I have a CASE (  Case # - 00233458 Send SNMP trap DateTime format issue    [ ref:_00D506e2N._5002J1C3njU:ref ] ) opened with SolarWinds Technical Support as well for the same, but the issue resolution is taking lot of time. There is a system down situation at our side due to this integration failing. The Trap flow is currently stopped, which impairs our operations personnel from having required network visibility. If the issue resolution is going to incur more delays, we might be staring at a revenue impact as well, as we are not able to deliver on the promise made to the customer. Any help will be highly appreciated.

I don't know how to control how the dates are formatted when traps are sent. However if you want to pursue the Custom SWQL option, then you can use a CASE expression to convert the numerical month to an English name. Like this:

CONCAT(

    CASE WHEN DAY(GETUTCDATE()) < 10 THEN '0' ELSE '' END, DAY(GETUTCDATE()), ' ',

    CASE MONTH(GETUTCDATE())

        WHEN 1 THEN 'January'

        WHEN 2 THEN 'February'

        WHEN 3 THEN 'March'

        WHEN 4 THEN 'April'

        WHEN 5 THEN 'May'

        WHEN 6 THEN 'June'

        WHEN 7 THEN 'July'

        WHEN 8 THEN 'August'

        WHEN 9 THEN 'September'

        WHEN 10 THEN 'October'

        WHEN 11 THEN 'November'

        WHEN 12 THEN 'December'

    END, ' ',

    YEAR(GETUTCDATE()), ' ',

    CASE WHEN HOUR(GETUTCDATE()) < 10 THEN '0' ELSE '' END, HOUR(GETUTCDATE()), ':',

    CASE WHEN MINUTE(GETUTCDATE()) < 10 THEN '0' ELSE '' END, MINUTE(GETUTCDATE())

)

I used "GETUTCDATE()" as the source of the date, but you can replace all of those mentions with AlertTriggerTime or whatever source column.

This is a bit more verbose than it would be if SWQL had more robust/flexible string formatting options. The bit to zero-pad the day, hour, and minute is particularly ugly, but it works and I think it's the best we can do with currently-available functions.

Thank you tdanner . Much appreciated help. This guidance is worth in gold for me. Can you guide me on which Orion tables does the AlterVariable Macro AlterTriggerTime use or the backend code for it ? When I used ${AlterTriggerTime} inside the SWQL function of HOUR, MONTH etc, it gets resolved, but treats the SWQL Code as just strings. So the entire content of the SNMP Trap comes out like below.

Message Type: Trap2Message

Time Received: 1/4/2019 11:57:32 AM

SNMP Version: Two

Origin Address/Port: 127.0.0.1:161

Destination Address/Port: 127.0.0.1:12345

Community: public

Id: 1

Variable IIDs and Values:

    1.3.6.1.6.3.1.1.4.3.0 (snmpTrapEnterprise): 1.3.6.1.4.1.11307

    1.3.6.1.4.1.11307.10.2: VLAN1 - VL1

    1.3.6.1.4.1.11307.10.3: 172.16.1.27

    1.3.6.1.4.1.11307.10.4: 14

    1.3.6.1.4.1.11307.10.5: VLAN1 - VL1

    1.3.6.1.4.1.11307.10.6: Interface

    1.3.6.1.4.1.11307.10.7: 534

    1.3.6.1.4.1.11307.10.1: {"AlertID":"47",

"Severity":"Critical",

"DateTime1":"16 November 2017 12:26",

"DateTime2":"16/11/2017 12:26:51"

"DateTime3":"${N=SWQL;M=SELECT CONCAT(

    CASE WHEN DAY(16 November 2017 12:26) < 10 THEN '0' ELSE '' END, DAY(16 November 2017 12:26), ' ',

    CASE MONTH()

        WHEN 1 THEN 'January'

        WHEN 2 THEN 'February'

        WHEN 3 THEN 'March'

        WHEN 4 THEN 'April'

        WHEN 5 THEN 'May'

        WHEN 6 THEN 'June'

        WHEN 7 THEN 'July'

        WHEN 8 THEN 'August'

        WHEN 9 THEN 'September'

        WHEN 10 THEN 'October'

        WHEN 11 THEN 'November'

        WHEN 12 THEN 'December'

    END, ' ',

    YEAR(16 November 2017 12:26), ' ',

    CASE WHEN HOUR(16 November 2017 12:26) < 10 THEN '0' ELSE '' END, HOUR(16 November 2017 12:26), ':',

    CASE WHEN MINUTE(16 November 2017 12:26) < 10 THEN '0' ELSE '' END, MINUTE(16 November 2017 12:26)

)} ",

"AlertTriggerCount":"1",

"AlertName":"Interface is down",

"AlertMessage":"Interface is down",

"AlertDescription":"This alert will write to the SolarWinds event log when an interface goes down and again when the interface comes back up.",

"Reset": "1"}

Description:

SysUpTime: 0

OID: 1.3.6.1.4.1.11307.10

The Actual code used is like below:

{"AlertID":"${N=Alerting;M=AlertID}",

"Severity":"${N=Alerting;M=Severity}",

"DateTime1":"${N=Alerting;M=AlertTriggerTime;F=DateTime}",

"DateTime2":"${N=Alerting;M=AlertTriggerTime;F=OriginalValue}"

"DateTime3":"${N=SWQL;M=SELECT CONCAT(

    CASE WHEN DAY(${N=Alerting;M=AlertTriggerTime;F=DateTime}) < 10 THEN '0' ELSE '' END, DAY(${N=Alerting;M=AlertTriggerTime;F=DateTime}), ' ',

    CASE MONTH()

        WHEN 1 THEN 'January'

        WHEN 2 THEN 'February'

        WHEN 3 THEN 'March'

        WHEN 4 THEN 'April'

        WHEN 5 THEN 'May'

        WHEN 6 THEN 'June'

        WHEN 7 THEN 'July'

        WHEN 8 THEN 'August'

        WHEN 9 THEN 'September'

        WHEN 10 THEN 'October'

        WHEN 11 THEN 'November'

        WHEN 12 THEN 'December'

    END, ' ',

    YEAR(${N=Alerting;M=AlertTriggerTime;F=DateTime}), ' ',

    CASE WHEN HOUR(${N=Alerting;M=AlertTriggerTime;F=DateTime}) < 10 THEN '0' ELSE '' END, HOUR(${N=Alerting;M=AlertTriggerTime;F=DateTime}), ':',

    CASE WHEN MINUTE(${N=Alerting;M=AlertTriggerTime;F=DateTime}) < 10 THEN '0' ELSE '' END, MINUTE(${N=Alerting;M=AlertTriggerTime;F=DateTime})

)} ",

"AlertTriggerCount":"${N=Alerting;M=AlertTriggerCount}",

"AlertName":"${N=Alerting;M=AlertName}",

"AlertMessage":"${N=Alerting;M=AlertMessage}",

"AlertDescription":"${N=Alerting;M=AlertDescription}",

"Reset": "1"}

If I just copy the "SELECT CONCAT(CASE WHEN DAY(16 November 2017 12:26) ..." query out of your message and try to run it in SWQL Studio, I get this error:

     no viable alternative at input 'November' in Select clause

Which makes sense. The solution is to wrap it in quotes. So in the trap template, everywhere you have ${N=Alerting;M=AlertTriggerTime;F=DateTime}, we actually need it to be '${N=Alerting;M=AlertTriggerTime;F=DateTime}' with the 'quotes' included.

Next, while SQL allows you to have a query that is just a SELECT with no FROM, SWQL does not. So we have to add a FROM even though we aren't looking at any columns. We only want one row returned, so we can include "TOP 1". SWQL also requires that all of the SELECT columns have an alias, so we need to add "AS x". Putting that together, we get this:

${N=SWQL;M=SELECT TOP 1 CONCAT(

    CASE WHEN DAY('${N=Alerting;M=AlertTriggerTime;F=DateTime}') < 10 THEN '0' ELSE '' END, DAY('${N=Alerting;M=AlertTriggerTime;F=DateTime}'), ' ',

    CASE MONTH()

        WHEN 1 THEN 'January'

        WHEN 2 THEN 'February'

        WHEN 3 THEN 'March'

        WHEN 4 THEN 'April'

        WHEN 5 THEN 'May'

        WHEN 6 THEN 'June'

        WHEN 7 THEN 'July'

        WHEN 8 THEN 'August'

        WHEN 9 THEN 'September'

        WHEN 10 THEN 'October'

        WHEN 11 THEN 'November'

        WHEN 12 THEN 'December'

    END, ' ',

    YEAR('${N=Alerting;M=AlertTriggerTime;F=DateTime}'), ' ',

    CASE WHEN HOUR('${N=Alerting;M=AlertTriggerTime;F=DateTime}') < 10 THEN '0' ELSE '' END, HOUR('${N=Alerting;M=AlertTriggerTime;F=DateTime}'), ':',

    CASE WHEN MINUTE('${N=Alerting;M=AlertTriggerTime;F=DateTime}') < 10 THEN '0' ELSE '' END, MINUTE('${N=Alerting;M=AlertTriggerTime;F=DateTime}') AS x FROM Orion.Nodes

)} "

Thank you tdanner​. Much appreciated guidance. The code with a sample value for ${N=Alerting;M=AlertTriggerTime;F=DateTime} still doesn't work. The SWQL code is being treated like a string I believe. The output comes out like the source of the code itself, only thing which resolves is the AlertVariable defined.

Wondering, Is there any known bug for 12.2 that SWQL based custom AlertVariable doesn't work or something ? I had tried SQL but then the HOUR, MINUTE and some of the SWQL specific functions doesn't work. Also I believe, the output from ${N=Alerting;M=AlertTriggerTime;F=DateTime}  is not what functions like DATEPART is expecting. So I keep on getting an error expecting integer or something similar.

Also, another thing which is really intriguing is that, the below code works very well in SWQL Studio with a sample value supplied for ${N=Alerting;M=AlertTriggerTime;F=DateTime} , but when copied over to define a custom AlertVariable, it gets treated just like a string, not a SWQL Code. Not sure what exactly is going crazy here.

If not SWQL, Is there a way to write this custom AlertVariable definition leveraging SQL code Tim ?

Thank you.

Regards,

Sriraj M

tdanner​ Any chance you got an opportunity to look at my above request ? What I'm finding is when I use the {N=SWQL;M=SELECT ... } option or {SWQL:SELECT ... } option, the UI doesn't seem like recognising the piece of string as a SWQL Code, instead it gets treated as string. So the source code itself comes out as a string in the SNMP Trap output, with the select AlertVariable {N=Alerting;M=AlertTriggerTime;F=DateTime} correctly resolving.

Now, I tried using {N=SQL;M=SELECT .. } option or {SQL:SELECT ... } option and I'm finding that the UI does recognise that the string is a SQL code and executes it. So I believe the way forward might be to leverage SQL code instead of SWQL. I prefer SWQL as it provides functions like HOUR, MONTH, DAY etc OOTB. If possible, Could you please convert the SWQL code you had shared into a SQL version of it and share it back with me ?

Thanks a million for your help & support Tim. Definitely worth in Gold for me, for sure.

Thank you.

Regards,

Sriraj M

This is actually much easier in SQL since we can use the FORMAT function. Like this:

SELECT FORMAT(CONVERT(datetime, '16 November 2017 12:26'), 'dd MMMM yyyy hh:mm')

Putting it into macro syntax, we would use something like this: ${SQL: SELECT FORMAT(CONVERT(datetime, '${N=Alerting;M=AlertTriggerTime;F=DateTime}'), 'dd MMMM yyyy hh:mm')}

tdanner​ Thank you Tim. The code for sure works without any SQL Macro errors in my local installation. The one which works fine is the DateTime3, which works awesome in my local laptop installation. In my local laptop installation the F=DateTime format is already like dd MMMM yyyy hh:mm. The code works like a charm.

Now when I copied over the customer format of Wednesday, October 31, 2018 12:40 PM in place of ${N=Alerting;M=AlertTriggerTime;F=DateTime}, the code breaks. It gives the below error. Do we need to apply any further functions ?

{"AlertID":"${N=Alerting;M=AlertID}",

"Severity":"${N=Alerting;M=Severity}",

"DateTime1":"${N=Alerting;M=AlertTriggerTime;F=DateTime}",

"DateTime2":"${SQL:SELECT (DATEPART(DAY,'${N=Alerting;M=AlertTriggerTime;F=Date}'))} ${N=Alerting;M=AlertTriggerTime;F=Month} ${N=Alerting;M=AlertTriggerTime;F=Time}",

"DateTime3":"${SQL: SELECT FORMAT(CONVERT(datetime, '${N=Alerting;M=AlertTriggerTime;F=DateTime}'), 'dd MMMM yyyy hh:mm')}",

"DateTime4":"${SQL: SELECT FORMAT(CONVERT(datetime, 'Wednesday, October 31, 2018 12:40 PM'), 'dd MMMM yyyy hh:mm')}",

"AlertTriggerCount":"${N=Alerting;M=AlertTriggerCount}",

"AlertName":"${N=Alerting;M=AlertName}",

"AlertMessage":"${N=Alerting;M=AlertMessage}",

"AlertDescription":"${N=Alerting;M=AlertDescription}",

"Reset": "1"}

Message Type: Trap2Message

Time Received: 1/9/2019 12:08:28 PM

SNMP Version: Two

Origin Address/Port: 127.0.0.1:161

Destination Address/Port: 127.0.0.1:12345

Community: public

Id: 1

Variable IIDs and Values:

    1.3.6.1.6.3.1.1.4.3.0 (snmpTrapEnterprise): 1.3.6.1.4.1.11307

    1.3.6.1.4.1.11307.10.2: FastEthernet0/13 - Fa0/13

    1.3.6.1.4.1.11307.10.3: XXXXXXXXX

    1.3.6.1.4.1.11307.10.4: 14

    1.3.6.1.4.1.11307.10.5: FastEthernet0/13 - Fa0/13

    1.3.6.1.4.1.11307.10.6: Interface

    1.3.6.1.4.1.11307.10.7: 536

    1.3.6.1.4.1.11307.10.1: {"AlertID":"47",

"Severity":"Critical",

"DateTime1":"16 November 2017 12:26",

"DateTime2":"16 November 2017 12:26",

"DateTime3":"16 November 2017 12:26",

"DateTime4":"MACRO SQL ERROR - Conversion failed when converting date and/or time from character string.",

"AlertTriggerCount":"1",

"AlertName":"Interface is down",

"AlertMessage":"Interface is down",

"AlertDescription":"This alert will write to the SolarWinds event log when an interface goes down and again when the interface comes back up.",

"Reset": "1"}

Description:

SysUpTime: 0

OID: 1.3.6.1.4.1.11307.10

Thanks a million Tim for your continued help, support and guidance on this. I'm very sure, without your continued help and guidance, I would have been like a fish out of water.

These culture differences are making this harder! Let's try this:

${SQL: SELECT FORMAT(PARSE('${N=Alerting;M=AlertTriggerTime;F=DateTime}' AS datetime USING 'en-US'), 'dd MMMM yyyy hh:mm')}

With this syntax we are telling SQL Server to use the US culture when converting the AlertTriggerTime from a string to a SQL datetime, and then the explicit format when converting it ​back​ to a string.