Need assistance with an SQL query because I could not seem to create a report for my needs.

Hello all.

I am trying to create a report that shows me this breakdown:

I have a customer with various sites. At these sites they have equipment that load balances. So the devices at the sites have two or more interfaces for internet connectivity. 1 as primary, 1 as a secondary.

If one interface goes down, it switches to the other, so the 'site' or 'node' does not go down, as it is now running on the secondary interface.

I am trying to create an availability report that shows the following:

For that customer, a breakdown of Each Location, the nodes at that location, the interfaces on those nodes. We have a custom field for Each Location named CustID, the node field is NodeID, and the interfaces are InterfaceID.

The report needs to show a breakdown by month, for the last 12 months, by location, by node (with availability for that month), listing each interface (with availability for that month), including the up/down times showing how long that interface or node was down.

I see we have a report for availability (with interfaces), but that does not run quickly as it includes ALL nodes and I only want it for this one customer, and since it will never finish running because it times out (we have over 57k objects), I can't even see what detail that report provides. The report I download from Thwack and tried was Group+Member+Availability+Report+[Interfaces].xml. IT never finishes running.

If someone already has a report for this, please advise or share Otherwise, can someone assist with adding the proper tables and detail to my sql query to get this info via SQL? OR help me convert it to SWQL?

Here's my SQL query as it stands now:

WITH NodeAvailability AS (
SELECT
YEAR(ia.Timestamp) AS Year,
MONTH(ia.Timestamp) AS Month,
ncp.Parent_Account_Number,
n.NodeID,
n.Caption AS NodeName,
'Node' AS ObjectType,
AVG(ia.Availability) AS Availability
FROM
Nodes n
INNER JOIN
NodesCustomProperties ncp ON n.NodeID = ncp.NodeID
INNER JOIN
InterfaceAvailability_CS_Detail ia ON n.NodeID = ia.NodeID
WHERE
n.Caption LIKE '%smoothie%'
GROUP BY
YEAR(ia.Timestamp),
MONTH(ia.Timestamp),
ncp.Parent_Account_Number,
n.NodeID,
n.Caption
),
InterfaceAvailability AS (
SELECT
YEAR(ia.Timestamp) AS Year,
MONTH(ia.Timestamp) AS Month,
ncp.Parent_Account_Number,
i.NodeID,
i.InterfaceID,
i.FullName AS InterfaceName,
'Interface' AS ObjectType,
AVG(ia.Availability) AS Availability
FROM
Interfaces i
INNER JOIN
Nodes n ON i.NodeID = n.NodeID
INNER JOIN
NodesCustomProperties ncp ON n.NodeID = ncp.NodeID
INNER JOIN
InterfaceAvailability_CS_Detail ia ON i.InterfaceID = ia.InterfaceID
WHERE
n.Caption LIKE '%smoothie%'
GROUP BY
YEAR(ia.Timestamp),
MONTH(ia.Timestamp),
ncp.Parent_Account_Number,
i.NodeID,
i.InterfaceID,
i.FullName
),
NodeAvailabilityTotal AS (
SELECT
Year,
Month,
Parent_Account_Number,
NodeID,
AVG(Availability) AS NodeAvailability
FROM
NodeAvailability
GROUP BY
Year,
Month,
Parent_Account_Number,
NodeID
)
SELECT
Year,
Month,
Parent_Account_Number,
NodeID,
'Node' AS ObjectType,
NULL AS InterfaceID,
NULL AS InterfaceName,
NodeAvailability AS Availability
FROM
NodeAvailabilityTotal
UNION ALL
SELECT
Year,
Month,
Parent_Account_Number,
NodeID,
'Interface' AS ObjectType,
InterfaceID,
InterfaceName,
Availability
FROM
InterfaceAvailability
ORDER BY
Year,
Month,
Parent_Account_Number,
NodeID,
ObjectType,
InterfaceID;

Find more posts tagged with

swql query

Availability

Reports

sql

SQL jobs

Reporting

npm

sql table

Accepted answers

All comments

leonejames1

I DO have this report, but I need to add interfaces, the times associated with those being up/down, and a sum at the bottom for each site (total down)

I can't figure out how to get that into my report.

leonejames1

It's not as fancy and pretty as the reports, but I am close using a sql query. but now I need to add the up/down info as shown in the first snapshot report. Here is what I have so far with the query:

WITH NodeData AS (

SELECT

YEAR(ia.Timestamp) AS Year,

DATENAME(MONTH, ia.Timestamp) AS Month,

ncp.Parent_Account_Number,

n.NodeID,

'Node' AS ObjectType,

NULL AS InterfaceID,

NULL AS InterfaceName,

AVG(ia.Availability) AS Availability,

ncp.Parent_Account_Name

FROM

Nodes n

INNER JOIN

NodesCustomProperties ncp ON n.NodeID = ncp.NodeID

INNER JOIN

InterfaceAvailability_CS_Detail ia ON n.NodeID = ia.NodeID

WHERE

n.Caption LIKE '%smoothie%'

GROUP BY

YEAR(ia.Timestamp),

DATENAME(MONTH, ia.Timestamp),

ncp.Parent_Account_Number,

n.NodeID,

ncp.Parent_Account_Name

InterfaceData AS (

SELECT

YEAR(ia.Timestamp) AS Year,

DATENAME(MONTH, ia.Timestamp) AS Month,

ncp.Parent_Account_Number,

i.NodeID,

'Interface' AS ObjectType,

i.InterfaceID,

i.FullName AS InterfaceName,

AVG(ia.Availability) AS Availability,

ncp.Parent_Account_Name

FROM

Interfaces i

INNER JOIN

Nodes n ON i.NodeID = n.NodeID

INNER JOIN

NodesCustomProperties ncp ON n.NodeID = ncp.NodeID

INNER JOIN

InterfaceAvailability_CS_Detail ia ON i.InterfaceID = ia.InterfaceID

WHERE

n.Caption LIKE '%smoothie%'

GROUP BY

YEAR(ia.Timestamp),

DATENAME(MONTH, ia.Timestamp),

ncp.Parent_Account_Number,

i.NodeID,

i.InterfaceID,

i.FullName,

ncp.Parent_Account_Name

)

SELECT

Year,

Month,

Parent_Account_Number,

NodeID,

ObjectType,

InterfaceID,

InterfaceName,

Availability,

Parent_Account_Name

FROM

(

SELECT * FROM NodeData

UNION ALL

SELECT * FROM InterfaceData

) AS CombinedData

ORDER BY

Year,

Month,

Parent_Account_Number,

NodeID,

CASE WHEN ObjectType = 'Node' THEN 0 ELSE 1 END, -- Ensure nodes are listed first

ObjectType;

KMSigma.SWI

What EXACTLY would you like this to look like? Maybe add a table to the reply. What entity type is the CustID assigned to?

leonejames1

Ideally? similar to the first image... In this type of format and containing this information: I created an example layout

leonejames1

The original report query from the 1st image above is posted here below. Not sure how to pull in the interface detail for the up/down .

select n.caption as [Device]
, '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]
, n.DetailsUrl as [_linkfor_Device]
, isnull(tostring(t2.[Down Event]),concat('Greater than ',(SELECT CurrentValue FROM Orion.Settings where settingid='SWNetPerfMon-Settings-Retain Events'),' days ago')) as [Down Event]
, isnull(tostring(t2.[Up Event]),'Still Down') as [Up Event]

, isnull(MINUTEDIFF(t2.[Down Event], isnull(t2.[Up Event],GETUTCDATE())),99999) as Minutes

from orion.nodes n
left join (SELECT
-- Device nodeid used for our join
StartTime.Nodes.NodeID

-- Down Event time stamp in local time zone
,ToLocal(StartTime.EventTime) AS [Down Event]

-- Up Event time stamp in local time zone
,(SELECT TOP 1
ToLocal(EventTime) AS [EventTime]
FROM Orion.Events AS [EndTime]
-- picks the first up event that is newer than the down event for this node
WHERE EndTime.EventTime >= StartTime.EventTime
-- EventType 5 is a node up
AND EndTime.EventType = 5
AND EndTime.NetObjectID = StartTime.NetObjectID
AND EventTime IS NOT NULL
ORDER BY EndTime.EventTime
) AS [Up Event]

-- This is the table we are querying
FROM Orion.Events StartTime

-- EventType 1 is a node down
WHERE StartTime.EventType = 1

) t2 on n.NodeID = t2.nodeid

-- this is how I catch nodes that are down but have aged out of the events table
where (n.status = 2 or t2.nodeid is not null) AND ((n.Caption like '%smoothie%') OR (n.Caption LIKE '%dyne%') OR (n.Caption LIKE '%ovation hospitality%') OR (n.Caption LIKE '%janski%'))

-- If you want to filter the results to only show outages of a minimum duration uncomment the below line
--and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) > 60

-- if you want to use this query in a search box of the Custom Query resource uncomment the below line
--and n.Caption like '%${SEARCH_STRING}%'

order by t2.[down event] desc