• Replies 2 replies
  • Subscribers 48 subscribers
  • Views 136 views
  • Users 0 members are here
Options
Related

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

kheekhaa

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;

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

Parents

  • 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;

Reply Children
No Data

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 200,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification SolarWinds Lab Link Accounts
About THWACK Blogs Federal & Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2021 SolarWinds Worldwide, LLC. All Rights Reserved.