Submit a ticketCall us

Don’t fall victim to a ransomware attack
Backups are helpful, but sometimes that’s not enough to protect your business against ransomware. At our live webcast we will discuss how to protect against ransomware attacks with SolarWinds® Patch Manager and how to leverage log data to detect ransomware. Register now for our live webcast.

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: 11 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