Help with SQL alert AND 'NodesCustomProperties='24/7'

sja

I need to add that to that SQL alert

'NodesCustomProperties='24/7'

select nodes.NodeID, nodes.Caption, CPUMultiLoad.TimeStampUTC, CPUMultiLoad.AvgLoad

from Nodes

join CPUMultiLoad on Nodes.NodeID = CPUMultiLoad.NodeID

where DATEDIFF(mi, CPUMultiLoad.TimeStampUTC, SYSUTCDATETIME()) < 5

and CPUMultiLoad.AvgLoad > 85

Find more posts tagged with

custom_swql_query

Accepted answers

All comments

dgsmith80

Just to clarify, if you want to add a filter to your SQL Query that uses a custom property. In your example is the custom property actually called NodesCustomProperties? As that is the name of the SQL Table?

SELECT nodes.NodeID, nodes.Caption, CPUMultiLoad.TimeStampUTC, CPUMultiLoad.AvgLoadFROM NodesJOIN CPUMultiLoad ON Nodes.NodeID = CPUMultiLoad.NodeIDWHERE DATEDIFF(mi, CPUMultiLoad.TimeStampUTC, SYSUTCDATETIME()) < 5 AND CPUMultiLoad.AvgLoad < 85 AND CustomPropertyName LIKE '24x7'

The Nodes table is actually a View Table and so it has some inherent joins, which include the custom properties, so you don't need to perform any additional joins for that. All you need to do is add the Name and the Content to filter on.

If however your just looking to include the Custom Property into the Alert then you can insert using the Variable:

${N=SwisEntity;M=CustomProperties.NAME}

Again just change NAME to the name of your Custom Property.

sja

Yes Thanks David

I think I miss the SQL table for CP ?right ?

SELECT nodes.NodeID, nodes.Caption, CPUMultiLoad.TimeStampUTC, CPUMultiLoad.AvgLoad ,"SQL CustomPropertyName table"
FROM Nodes
JOIN CPUMultiLoad ON Nodes.NodeID = CPUMultiLoad.NodeID
WHERE DATEDIFF(mi, CPUMultiLoad.TimeStampUTC, SYSUTCDATETIME()) < 5 AND CPUMultiLoad.AvgLoad < 85 AND CustomPropertyName LIKE'24x7'

j_a_catlin

Nodes is a view and not a table. It already joins multiple node related tables including the nodescustomproperties table in it, so there is no need to join that table.

You don't actually modify the select portion on a custom sql alert, so there is no real reason to have to worry about what is called in the select. All of the customization and trigger conditions for an alert happens in the join and where clause. What needs to be clarified is what is the name of the custom property you are trying to filter based on and what the value for that filter is. Based on your question, it would be like you named the custom property you built is called NodesCustomProperties and the value you are trying to filter is 24/7 so in the where clause it would be NodesCustomProperties='24/7'. I will use that as the example. If you named the custom property 24/7 or something like that it and it is a yes/no then you would need to filter by using 24/7=1 if it were for nodes with a yes and 24/7=0 for nodes with a no

So if this is a node based alert, you would be looking at the following when you enter it:

Unmodified top section:

SELECT Nodes.Caption, Nodes.NodeID FROM Nodes

Section you can modify in a custom SQL alert:

JOIN CPUMultiLoad ON Nodes.NodeID = CPUMultiLoad.NodeID

WHERE DATEDIFF(mi, CPUMultiLoad.TimeStampUTC, SYSUTCDATETIME()) < 5 AND CPUMultiLoad.AvgLoad < 85 AND NodesCustomProperties='24/7'