Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > Netflow Traffic Analyzer (NTA) > Unmonitored Ports SQL Query report for NTA 3.11

Unmonitored Ports SQL Query report for NTA 3.11

Created by Eric Bryant, last modified by MindTouch on Jun 23, 2016

Views: 6 Votes: 0 Revisions: 6

Overview

Unmonitored ports section does not display a port number for unmonitored traffic section in Top Applications resources.

 

unmonitored traffic.PNG

Environment

NTA 3.XX or earlier

Resolution

Note: This is a possible workaround for the issue.

 

Run the following query on the SQL Management Studio to collect unknown traffic port information.

 

SET NOCOUNT ON:
-- NTA Reporting TOP Query - ApplicationsDAL
-- Count: 5, Rx: True, Tx: True, Absolute: True, TopKey: 

-- Limitations table
CREATE TABLE #NetflowSources (InterfaceID int NOT NULL)
INSERT INTO #NetflowSources
SELECT ns.InterfaceID FROM NetFlowSources ns
    JOIN Interfaces ON Interfaces.InterfaceID = ns.InterfaceID
    JOIN Nodes ON Nodes.NodeID = Interfaces.NodeID
WHERE Enabled=1        

-- Top query for Top Applications

--Temp tables creation
CREATE TABLE #TopPortspre
(
    Port int NOT NULL,
    InterfaceID int NULL,
    NodeID int NULL,
    SourceIP varchar(100) NOT NULL,
    DestinationIP varchar(100) NOT NULL,
    TotalBytes bigint NOT NULL,
    TotalBytesIngress bigint NOT NULL,
    TotalBytesEgress bigint NOT NULL
)

CREATE TABLE #Apps
(
    AppID varchar(7),
    KeyRank bigint NOT NULL,
    TotalBytesIngress bigint NOT NULL,
    TotalBytesEgress bigint NOT NULL
)


--Declarations
DECLARE @Tables CURSOR;
DECLARE @TableName varchar(100)
DECLARE @NodeID varchar(100)
DECLARE @cmd varchar(max)
DECLARE @WithNoLock varchar(20)

DECLARE @StartTime datetime;
DECLARE @EndTime datetime;

set @StartTime = DATEADD(DAY, -1, GETDATE());
set @EndTime = GETDATE();

EXEC dbo.swsp_NetFlowGetParametersForDALs
    @DateStart = @StartTime,
    @DateEnd = @EndTime,
    @Nodes = '',
    @Interfaces = '',
    @Views = @Tables OUT

FETCH FROM @Tables INTO @TableName, @NodeID;
WHILE @@FETCH_STATUS = 0
BEGIN        
    IF (@TableName='NetFlowSummary1' OR @TableName='NetFlowSummary2' OR @TableName='NetFlowSummary3')
      SET @WithNoLock = ' WITH (NOLOCK) ' 
    ELSE 
      SET @WithNoLock = '' 

    SET @cmd = 'INSERT INTO #TopPortspre';

    
SET @cmd = @cmd + '
SELECT  Port AS [Port],
        NULL AS InterfaceID, NULL AS NodeID,
        SourceIP, DestinationIP,
        SUM(TotalBytesIngress + TotalBytesEgress) AS TotalBytes,
        SUM(TotalBytesIngress) AS TotalBytesIngress,
        SUM(TotalBytesEgress) AS TotalBytesEgress
    FROM
    (
        SELECT
            CASE WHEN SourcePort=0 THEN DestPort ELSE SourcePort END AS Port,
            NULL AS InterfaceID, NULL AS NodeID,
            SourceIPSort AS SourceIP, DestIPSort AS DestinationIP,
            SUM(TotalBytesIngress) AS TotalBytesIngress,
            SUM(TotalBytesEgress) AS TotalBytesEgress
        FROM
        (
            
            SELECT 
                InterfaceIDRx AS InterfaceID,
                TotalBytes AS TotalBytesIngress,
                0 AS TotalBytesEgress,
                *
             FROM 
                 ' + @TableName + '
             WHERE (Protocol=6 OR Protocol=17) AND ( StartTime >= ''' + CONVERT(VARCHAR, @StartTime, 120) + ''' AND StartTime < ''' + CONVERT(VARCHAR, @EndTime, 120) + ''' ) AND  ( ((SourcePort<>0 AND NOT EXISTS(SELECT TOP 1 MapTo FROM NetFlowApps WHERE Enabled=1 AND MapTo=SourcePort AND ((TCP * 6=Protocol) OR (UDP*17=Protocol)) ))  OR (DestPort<>0 AND NOT EXISTS(SELECT TOP 1 MapTo FROM NetFlowApps WHERE Enabled=1 AND MapTo=DestPort AND ((TCP * 6=Protocol) OR (UDP*17=Protocol)) )) ) ) 

        )  TopXXTable
        
        --joining with limitations information for top table
        INNER JOIN #NetflowSources ns ON ns.InterfaceID = TopXXTable.InterfaceID
        WHERE (1=1) 
        GROUP BY CASE WHEN SourcePort=0 THEN DestPort ELSE SourcePort END, SourceIPSort, DestIPSort

UNION ALL

        SELECT
            CASE WHEN SourcePort=0 THEN DestPort ELSE SourcePort END AS Port,
            NULL AS InterfaceID, NULL AS NodeID,
            SourceIPSort AS SourceIP, DestIPSort AS DestinationIP,
            SUM(TotalBytesIngress) AS TotalBytesIngress,
            SUM(TotalBytesEgress) AS TotalBytesEgress
        FROM
        (
            
            SELECT 
                InterfaceIDTx AS InterfaceID,
                0 AS TotalBytesIngress,
                TotalBytes AS TotalBytesEgress,
                *
             FROM 
                 ' + @TableName + '
             WHERE (Protocol=6 OR Protocol=17) AND ( StartTime >= ''' + CONVERT(VARCHAR, @StartTime, 120) + ''' AND StartTime < ''' + CONVERT(VARCHAR, @EndTime, 120) + ''' ) AND  ( ((SourcePort<>0 AND NOT EXISTS(SELECT TOP 1 MapTo FROM NetFlowApps WHERE Enabled=1 AND MapTo=SourcePort AND ((TCP * 6=Protocol) OR (UDP*17=Protocol)) ))  OR (DestPort<>0 AND NOT EXISTS(SELECT TOP 1 MapTo FROM NetFlowApps WHERE Enabled=1 AND MapTo=DestPort AND ((TCP * 6=Protocol) OR (UDP*17=Protocol)) )) ) ) 

        )  TopXXTable
        
        --joining with limitations information for top table
        INNER JOIN #NetflowSources ns ON ns.InterfaceID = TopXXTable.InterfaceID
        WHERE (1=1) 
        GROUP BY CASE WHEN SourcePort=0 THEN DestPort ELSE SourcePort END, SourceIPSort, DestIPSort

    ) AS A

    GROUP BY Port, SourceIP, DestinationIP
    ORDER BY SUM(TotalBytesIngress + TotalBytesEgress) DESC
';

    EXEC (@cmd)
    
    FETCH FROM @Tables INTO @TableName, @NodeID;

END
CLOSE @Tables
DEALLOCATE @Tables

 

CREATE TABLE #TopApps2
(
    Port int,
    InterfaceID int NULL,
    NodeID int NULL,
    SourceIP varchar(100),
    DestinationIP varchar(100),
    TotalBytes bigint NOT NULL,
    TotalBytesIngress bigint NOT NULL,
    TotalBytesEgress bigint NOT NULL
)


INSERT INTO #TopApps2
SELECT 
    Port, 
    InterfaceID, 
    NodeID, 
    SourceIP, 
    DestinationIP,
    SUM(TotalBytes) AS TotalBytes, 
    SUM(TotalBytesIngress) AS TotalBytesIngress,
    SUM(TotalBytesEgress) AS TotalBytesEgress 
FROM #TopPortspre
GROUP BY Port, NodeID, InterfaceID, SourceIP, DestinationIP

INSERT INTO #Apps
SELECT TOP 1000 -- Can be changed based on requirement
    ISNULL(ap.AppID, CAST(TopApps.Port AS VARCHAR)) AS AppID,
    SUM(TotalBytes) AS KeyRank,
    SUM(TotalBytesIngress) AS TotalBytesIngress, 
    SUM(TotalBytesEgress) AS TotalBytesEgress
FROM #TopApps2 AS TopApps
LEFT JOIN NetFlowApps ap ON ap.MapTo = TopApps.Port
GROUP BY SourceIP, DestinationIP, ISNULL(ap.AppID, CAST(TopApps.Port AS VARCHAR))
ORDER BY SUM(TotalBytes) DESC

-- Select From Aggregate and Join


SELECT  NULL AS ApplicationName, 
        NULL AS PortNumber, 
        NULL AS SourceIP,
        NULL AS DestinationIP,
        KeysTable.KeyRank  AS TotalBytes,
        KeysTable.TotalBytesIngress AS TotalBytesIngress, 
        KeysTable.TotalBytesEgress AS TotalBytesEgress
    FROM #Apps KeysTable
    WHERE (AppID IS NULL)

UNION ALL


SELECT
    COALESCE(ap.AppName, N'Port ' + CAST(FlowStats.Port AS NVARCHAR)) AS ApplicationName,
    ISNULL(ap.MapTo, FlowStats.Port) AS PortNumber,    
    [dbo].[ConvertFromIPSort](FlowStats.SourceIP),
    [dbo].[ConvertFromIPSort](FlowStats.DestinationIP),
    SUM(TotalBytes) AS TotalBytes,
    SUM(FlowStats.TotalBytesIngress) AS TotalBytesIngress, 
    SUM(FlowStats.TotalBytesEgress) AS TotalBytesEgress
FROM #TopApps2 FlowStats
    LEFT OUTER JOIN NetFlowApps ap ON ap.MapTo = FlowStats.Port
    INNER JOIN #Apps AS KeysTable ON ((FlowStats.Port = KeysTable.AppID)) OR (ap.AppID = KeysTable.AppID)
GROUP BY
    KeysTable.KeyRank,
    COALESCE(ap.AppName, N'Port ' + CAST(FlowStats.Port AS NVARCHAR)),
    ISNULL(ap.MapTo, FlowStats.Port),
    FlowStats.SourceIP, FlowStats.DestinationIP,
    NodeID,
    InterfaceID
    
HAVING
(
    COALESCE(ap.AppName, N'Port ' + CAST(FlowStats.Port AS NVARCHAR)) IS NOT NULL
    AND ISNULL(ap.MapTo, FlowStats.Port) IS NOT NULL
    AND FlowStats.SourceIP IS NOT NULL
    AND FlowStats.DestinationIP IS NOT NULL
)
ORDER BY TotalBytes ASC

DROP TABLE #TopPortspre
DROP TABLE #Apps
DROP TABLE #TopApps2
DROP TABLE #NetflowSources

Last modified
21:00, 22 Jun 2016

Tags

Classifications

Public