Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Report for bandwidth usage on WAN circuits

I need either a SWQL report or SQL that will list bandwidth usage on WAN circuits, Leaving out all with no data, or 'zeros'.

Find more posts tagged with

Accepted answers

All comments

John W Johnson

There is already a report in SW...but so much devices with no data...how to remove those?

KMSigma.SWI

Create a custom property for Interfaces called "InterfaceType" or something like that.
Assign the outbound interfaces a setting of "WAN Circuit" (or something).
Duplicate the existing report and add an additional filters for Interfaces with only the InterfaceType Custom Property = "WAN Circuit"

John W Johnson

I actually have a SQL Query that gets me so close... it just lists everything...

 DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)
SET @EndDate = GetDate()

SELECT Interfaces.InterfaceId,
Nodes.Caption AS NodeName,       
Interfaces.Caption AS Interface_Caption,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95
FROM Nodes
INNER JOIN Interfaces
    ON (Nodes.NodeID = Interfaces.NodeID)
---------------------
INNER JOIN    
(
    SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
    ) AS AA
) as RESULT_IN
    ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)
---------------------
INNER JOIN    
(
    SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
    ) AS AA
) as RESULT_OUT
    ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
---------------------
INNER JOIN    
(
    SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
    ) AS AA
) as RESULT_MAX
    ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
---------------------
WHERE Interfaces.InterfaceType='WAN'
 ORDER BY NodeName, Interface_Caption</pre>However, when i run this, i get an error;Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'WAN' to data type int.

KMSigma.SWI

I think InterfaceType might already be a field (integer) that represents the physical type of interface (VLAN, ethernet, etc.). That's why I recommended a custom property. I also (personally) stay away from pure SQL reports as much as possible because the underlying data structures might change in a future release.

If you prefer to stay in SQL (not recommended), then you'll need to look for something in the Interfaces table that would uniquely identify your WAN circuits and use that for your WHERE clause.