We have been using Google Maps, but the cost next year will be excessive (2.5 million API calls/year) I have some complex SQL queries to do some creative things, like show one icon for down nodes, a different one if we failed over to the Cellular on a router, a different one if the Cellular interface is down, etc (small section of the SQL):
SELECT
CASE WHEN N.Status=2 THEN 5
WHEN N.Status=3 THEN 4
WHEN N.Status=12 THEN 3
WHEN N.Status=1 THEN
CASE
WHEN exists
(SELECT i.status from Interfaces i
join NodesData n2 WITH (NOLOCK) on n2.NodeID = i.NodeID
where n2.NodeID = n.NodeID
and (i.status = 2 AND i.caption like '%VZN-VPN:%')) THEN 5
WHEN exists
(SELECT i.status from Interfaces i
join NodesData n2 WITH (NOLOCK) on n2.NodeID = i.NodeID
where n2.NodeID = n.NodeID
and (i.status = 2 AND i.caption like '%VZN-VRF:%')) THEN 5
WHEN exists
(SELECT i.status from Interfaces i
join NodesData n2 WITH (NOLOCK) on n2.NodeID = i.NodeID
where n2.NodeID = n.NodeID
and (i.status = 2 AND i.caption like '%Cellular0/2/0 · VZW%')) THEN 5
WHEN EXISTS
(SELECT n2.nodeID from NodesData n2 WITH (NOLOCK)
JOIN APM_Application app WITH (NOLOCK) on app.NodeID = n2.NodeID
JOIN APM_Component c WITH (NOLOCK) on c.ApplicationID = app.ID
JOIN APM_CurrentComponentStatus cs WITH (NOLOCK) on cs.ComponentID = c.ID
WHERE app.Name = 'NEO Cellular'
AND c.Name = 'Branch Cellular'
AND cs.Availability = 5
AND n2.NodeID = n.NodeID) THEN 5
ELSE 2
END
WHEN N.Status=9 THEN 1
WHEN N.Status=0 THEN 0
ELSE N.Status
END AS Rank, N.NodeID,
CASE
WHEN exists
(SELECT i.status from Interfaces i
join NodesData n2 WITH (NOLOCK) on n2.NodeID = i.NodeID
where n2.NodeID = n.NodeID
and (i.status = 2 AND i.caption like '%VZN-VPN:%')) THEN 'SerialDown.gif'
WHEN exists
(SELECT i.status from Interfaces i
join NodesData n2 WITH (NOLOCK) on n2.NodeID = i.NodeID
where n2.NodeID = n.NodeID
and (i.status = 2 AND i.caption like '%VZN-VRF:%')) THEN 'SerialDown.gif'
WHEN EXISTS
(SELECT n2.nodeID from NodesData n2 WITH (NOLOCK)
JOIN APM_Application app WITH (NOLOCK) on app.NodeID = n2.NodeID
JOIN APM_Component c WITH (NOLOCK) on c.ApplicationID = app.ID
JOIN APM_CurrentComponentStatus cs WITH (NOLOCK) on cs.ComponentID = c.ID
WHERE app.Name = 'NEO Cellular'
AND c.Name = 'Branch Cellular'
AND cs.Availability = 5
AND n2.NodeID = n.NodeID) THEN 'SerialDown.gif'
WHEN exists
(SELECT i.status from Interfaces i
join NodesData n2 WITH (NOLOCK) on n2.NodeID = i.NodeID
where n2.NodeID = n.NodeID
and (i.status = 2 AND i.caption like '%Cellular0/2/0 · VZW%')) THEN 'CellDown.gif'
ELSE N.StatusLED
END as StatusLED,
.
.
.
I would like (and so would my manager) to switch it over to the free MapQuest Worldmap.
Where are the SQL queries to the DB held in the ASPX chain that pulls the device status from the database?
Thanks,
Michael
