Interface Utilization Report need for Business Hour (9x6) only

We have customs SWQL Query report for interface utilization , but need to create this report/data of Business Hour (9x6) time windows only and also on month basis (last month/this month), please suggest us what changes need to perform in below query.

------------------------------------------------

SELECT TOP 100

N.Caption,

N.IP_Address AS [Node IP],

NPMI.InBandwidth AS Bandwidth,

ROUND(AVG(nps.Inpercentutil), 2) AS AvgTxUtil,

ROUND(AVG(nps.Outpercentutil), 2) AS AvgRxUtil,

ROUND(MAX(nps.Inpercentutil), 2) AS MaxTxUtil,

ROUND(MAX(nps.Outpercentutil), 2) AS MaxRxUtil,

N.MachineType,

NIPA.IPAddress AS InterfaceIP,

NPMI.Name AS [Interface Name],

NPMI.InterfaceCaption

FROM

Orion.NodeIPAddresses NIPA

INNER JOIN Orion.NPM.Interfaces NPMI

ON NIPA.NodeID = NPMI.NodeID

AND NIPA.InterfaceIndex = NPMI.InterfaceIndex

INNER JOIN Orion.Nodes N

ON NIPA.NodeID = N.NodeID

INNER JOIN Orion.NodesCustomProperties NCP

ON N.NodeID = NCP.NodeID

INNER JOIN Orion.NPM.InterfaceTraffic nps

ON NPMI.InterfaceID = nps.InterfaceID

WHERE

N.CustomProperties.device_category LIKE 'Network'

AND NPMI.InBandwidth > 1000

AND nps.DateTime >= AddDay(-30, GETUTCDATE())

GROUP BY

N.Caption,

N.IP_Address,

NPMI.InBandwidth,

N.MachineType,

NIPA.IPAddress,

NPMI.Name,

NPMI.InterfaceCaption,

NPMI.OutBandwidth

ORDER BY

N.Caption ASC;

--------------------------------------------------

Top Replies

stevenstadel 17 days ago +3

Try this for the current month. SELECT TOP 100 N.Caption, N.IP_Address AS [Node IP], NPMI.InBandwidth AS Bandwidth, ROUND(AVG(nps.Inpercentutil), 2) AS AvgTxUtil, ROUND(AVG(nps.Outpercentutil), 2…

stevenstadel 17 days ago

Try this for the current month.

SELECT TOP 100
     N.Caption,
     N.IP_Address AS [Node IP],
     NPMI.InBandwidth AS Bandwidth,
     ROUND(AVG(nps.Inpercentutil), 2) AS AvgTxUtil,
     ROUND(AVG(nps.Outpercentutil), 2) AS AvgRxUtil,
     ROUND(MAX(nps.Inpercentutil), 2) AS MaxTxUtil,
     ROUND(MAX(nps.Outpercentutil), 2) AS MaxRxUtil,
     N.MachineType,
     NIPA.IPAddress AS InterfaceIP,
     NPMI.Name AS [Interface Name],
     NPMI.InterfaceCaption
FROM
     Orion.NodeIPAddresses NIPA
INNER JOIN Orion.NPM.Interfaces NPMI
    ON NIPA.NodeID = NPMI.NodeID
    AND NIPA.InterfaceIndex = NPMI.InterfaceIndex
INNER JOIN Orion.Nodes N
    ON NIPA.NodeID = N.NodeID
INNER JOIN Orion.NodesCustomProperties NCP
    ON N.NodeID = NCP.NodeID
INNER JOIN Orion.NPM.InterfaceTraffic nps
    ON NPMI.InterfaceID = nps.InterfaceID
WHERE
     N.CustomProperties.device_category LIKE 'Network'
     AND NPMI.InBandwidth > 1000
     -- Filter for the current month
     AND nps.DateTime >= DateTrunc('month', GetDate())  -- First day of the current month
     AND nps.DateTime < AddDate('month', 1, DateTrunc('month', GetDate()))  -- First day of next month
     -- Filter for business hours (9 AM to 6 PM)
     AND Hour(nps.DateTime) >= 9  -- 9 AM or later
     AND Hour(nps.DateTime) < 18  -- Before 6 PM
GROUP BY
     N.Caption,
     N.IP_Address,
     NPMI.InBandwidth,
     N.MachineType,
     NIPA.IPAddress,
     NPMI.Name,
     NPMI.InterfaceCaption,
     NPMI.OutBandwidth
ORDER BY
     N.Caption ASC;

Or this for previous month

SELECT TOP 100
     N.Caption,
     N.IP_Address AS [Node IP],
     NPMI.InBandwidth AS Bandwidth,
     ROUND(AVG(nps.Inpercentutil), 2) AS AvgTxUtil,
     ROUND(AVG(nps.Outpercentutil), 2) AS AvgRxUtil,
     ROUND(MAX(nps.Inpercentutil), 2) AS MaxTxUtil,
     ROUND(MAX(nps.Outpercentutil), 2) AS MaxRxUtil,
     N.MachineType,
     NIPA.IPAddress AS InterfaceIP,
     NPMI.Name AS [Interface Name],
     NPMI.InterfaceCaption
FROM
     Orion.NodeIPAddresses NIPA
INNER JOIN Orion.NPM.Interfaces NPMI
    ON NIPA.NodeID = NPMI.NodeID
    AND NIPA.InterfaceIndex = NPMI.InterfaceIndex
INNER JOIN Orion.Nodes N
    ON NIPA.NodeID = N.NodeID
INNER JOIN Orion.NodesCustomProperties NCP
    ON N.NodeID = NCP.NodeID
INNER JOIN Orion.NPM.InterfaceTraffic nps
    ON NPMI.InterfaceID = nps.InterfaceID
WHERE
     N.CustomProperties.device_category LIKE 'Network'
     AND NPMI.InBandwidth > 1000
     -- Filter for the previous month
     AND nps.DateTime >= AddDate('month', -1, DateTrunc('month', GetDate()))  -- First day of the previous month
     AND nps.DateTime < DateTrunc('month', GetDate())  -- First day of the current month
     -- Filter for business hours (9 AM to 6 PM)
     AND Hour(nps.DateTime) >= 9  -- 9 AM or later
     AND Hour(nps.DateTime) < 18  -- Before 6 PM
GROUP BY
     N.Caption,
     N.IP_Address,
     NPMI.InBandwidth,
     N.MachineType,
     NIPA.IPAddress,
     NPMI.Name,
     NPMI.InterfaceCaption,
     NPMI.OutBandwidth
ORDER BY
     N.Caption ASC;

If you want use Mon-Fri Business Hours for the current month

SELECT TOP 100
     N.Caption,
     N.IP_Address AS [Node IP],
     NPMI.InBandwidth AS Bandwidth,
     ROUND(AVG(nps.Inpercentutil), 2) AS AvgTxUtil,
     ROUND(AVG(nps.Outpercentutil), 2) AS AvgRxUtil,
     ROUND(MAX(nps.Inpercentutil), 2) AS MaxTxUtil,
     ROUND(MAX(nps.Outpercentutil), 2) AS MaxRxUtil,
     N.MachineType,
     NIPA.IPAddress AS InterfaceIP,
     NPMI.Name AS [Interface Name],
     NPMI.InterfaceCaption
FROM
     Orion.NodeIPAddresses NIPA
INNER JOIN Orion.NPM.Interfaces NPMI
    ON NIPA.NodeID = NPMI.NodeID
    AND NIPA.InterfaceIndex = NPMI.InterfaceIndex
INNER JOIN Orion.Nodes N
    ON NIPA.NodeID = N.NodeID
INNER JOIN Orion.NodesCustomProperties NCP
    ON N.NodeID = NCP.NodeID
INNER JOIN Orion.NPM.InterfaceTraffic nps
    ON NPMI.InterfaceID = nps.InterfaceID
WHERE
     N.CustomProperties.device_category LIKE 'Network'
     AND NPMI.InBandwidth > 1000
     -- Filter for the current month
     AND nps.DateTime >= DateTrunc('month', GetDate())  -- First day of the current month
     AND nps.DateTime < AddDate('month', 1, DateTrunc('month', GetDate()))  -- First day of next month
     -- Filter for business hours (9 AM to 6 PM)
     AND Hour(nps.DateTime) >= 9  -- 9 AM or later
     AND Hour(nps.DateTime) < 18  -- Before 6 PM
     -- Filter for Monday to Friday (WeekDay: 1=Mon, 5=Fri)
     AND WeekDay(nps.DateTime) >= 1  -- Monday or later
     AND WeekDay(nps.DateTime) <= 5  -- Friday or earlier
GROUP BY
     N.Caption,
     N.IP_Address,
     NPMI.InBandwidth,
     N.MachineType,
     NIPA.IPAddress,
     NPMI.Name,
     NPMI.InterfaceCaption,
     NPMI.OutBandwidth
ORDER BY
     N.Caption ASC;

Or this for Mon-Fri Business Hours for previous month

SELECT TOP 100
     N.Caption,
     N.IP_Address AS [Node IP],
     NPMI.InBandwidth AS Bandwidth,
     ROUND(AVG(nps.Inpercentutil), 2) AS AvgTxUtil,
     ROUND(AVG(nps.Outpercentutil), 2) AS AvgRxUtil,
     ROUND(MAX(nps.Inpercentutil), 2) AS MaxTxUtil,
     ROUND(MAX(nps.Outpercentutil), 2) AS MaxRxUtil,
     N.MachineType,
     NIPA.IPAddress AS InterfaceIP,
     NPMI.Name AS [Interface Name],
     NPMI.InterfaceCaption
FROM
     Orion.NodeIPAddresses NIPA
INNER JOIN Orion.NPM.Interfaces NPMI
    ON NIPA.NodeID = NPMI.NodeID
    AND NIPA.InterfaceIndex = NPMI.InterfaceIndex
INNER JOIN Orion.Nodes N
    ON NIPA.NodeID = N.NodeID
INNER JOIN Orion.NodesCustomProperties NCP
    ON N.NodeID = NCP.NodeID
INNER JOIN Orion.NPM.InterfaceTraffic nps
    ON NPMI.InterfaceID = nps.InterfaceID
WHERE
     N.CustomProperties.device_category LIKE 'Network'
     AND NPMI.InBandwidth > 1000
     -- Filter for the previous month
     AND nps.DateTime >= AddDate('month', -1, DateTrunc('month', GetDate()))  -- First day of the previous month
     AND nps.DateTime < DateTrunc('month', GetDate())  -- First day of the current month
     -- Filter for business hours (9 AM to 6 PM)
     AND Hour(nps.DateTime) >= 9  -- 9 AM or later
     AND Hour(nps.DateTime) < 18  -- Before 6 PM
     -- Filter for Monday to Friday (WeekDay: 1=Mon, 5=Fri)
     AND WeekDay(nps.DateTime) >= 1  -- Monday or later
     AND WeekDay(nps.DateTime) <= 5  -- Friday or earlier
GROUP BY
     N.Caption,
     N.IP_Address,
     NPMI.InBandwidth,
     N.MachineType,
     NIPA.IPAddress,
     NPMI.Name,
     NPMI.InterfaceCaption,
     NPMI.OutBandwidth
ORDER BY
     N.Caption ASC;

bobmarley 17 days ago in reply to stevenstadel

@stevenstadel Great work
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel