This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Having trouble with Custom SQL Alert

rmullal over 4 years ago

I have a SQL query that checks our store subnets for less than 128 IP addresses (we had an issue last week where some data mysteriously disappeared from IPAM awhile ago, and we just now realized it. so we would like to know immediately if this data goes missing again). I have it running as a report, but i would like to set it up as an alert instead. Here is the query:

SELECT COUNT(DISTINCT n.IPAddress) AS [Count],

g.FriendlyName as StoreNumber

FROM IPAM_Node n

JOIN [dbo].[IPAM_Group] g

ON n.SubnetId = g.GroupId

WHERE g.CIDR = 25

GROUP BY g.FriendlyName,

g.CIDR

HAVING COUNT(DISTINCT n.IPAddress) < 128;

Now i'm having trouble with setting it up as a new alert:

what should i use from the dropdown to make this validate?

Top Replies

i_like_eggs over 4 years ago +2

Unfortuntatly i dont have the IPAM module installed so i cant test, but i can see where you may be going wrong here, the alert trigger condtion when using custom SQL you need to 'Join' your condition on…

0 i_like_eggs over 4 years ago
Unfortuntatly i dont have the IPAM module installed so i cant test, but i can see where you may be going wrong here, the alert trigger condtion when using custom SQL you need to 'Join' your condition on to the pre populated SELECT. so something like this:
INNER JOIN IPAM_Node n ON IPAM_NOdeReportView.IPNodeId =n.nodeid
INNER JOIN IPAM_Group g ON n.SubnetId = g.GroupId
WHERE g.CIDR = 25
GROUP BY g.FriendlyName, g.CIDR
HAVING COUNT(DISTINCT n.IPAddress) < 128;
Cancel
Vote Up +2 Vote Down

Cancel
0 rmullal over 4 years ago in reply to i_like_eggs

You were close! i took yours and came up with this:
SELECT IPAM_GroupReportView.FriendlyName, IPAM_GroupReportView.GroupId FROM IPAM_GroupReportView
INNER JOIN IPAM_Group g ON IPAM_GroupReportView.GroupID = g.GroupId
INNER JOIN IPAM_Node n ON n.SubnetId = g.GroupId
WHERE g.CIDR = 25
GROUP BY g.FriendlyName, g.CIDR, IPAM_GroupReportView.FriendlyName, IPAM_GroupReportView.GroupId
HAVING COUNT(DISTINCT n.IPAddress) < 128

Thank you so much for getting me in the right direction!
PS: how do you format the code in Thwack and make it look all pretty?
Cancel
Vote Up 0 Vote Down

Cancel