Top Conversation or Endpoints Report with better performance

jan.turon

If you are looking for NTA Conversations Report that groups data by Source IP, Destination IP, Exporting node and lets you specify list of Endpoints (IP Addresses) you're interested in, consider creating a report using SWQL. 

Try entering the following SWQL into the reporting:

SELECT 
    [N].[Caption] AS ExportingNodeCaption, 
    [F2].[IP],
    [F2].[Hostname],
    [F2].[PartnerIP],
    [DNS1].[Hostname] AS PartnerHostname,
    [F2].[Bytes]
FROM
(   
    SELECT TOP 500 
		[F].[NodeID],
        [F].[IP],
        [F].[Hostname],
        [F].[PartnerIP], 
        SUM([F].[Bytes]) AS Bytes
    FROM 
        Orion.Netflow.FlowsByIP AS F
    WHERE 
        [F].[IP] IN ('192.168.100.1', '192.168.100.15', '192.168.100.16') OR 
        ([F].[IP] >= '192.168.2.0' AND [F].[IP] <= '192.168.2.255') OR 
        ([F].[IP] >= '192.168.0.0' AND [F].[IP] <= '192.168.0.255')
        AND ([F].[TimeStamp]) > ADDDATE('DAY', -1, GETUTCDATE())
    GROUP BY 
        [F].[NodeID],
        [F].[IP], 
        [F].[Hostname],
        [F].[PartnerIP]
    ORDER BY 
        SUM([F].[Bytes]) DESC
) AS F2
LEFT JOIN Orion.Nodes N ON [N].[NodeID] = [F2].[NodeID]

 

or you can simply Import this report: /cfs-file/__key/communityserver-discussions-components-files/7/Top_2B00_500_2B00_Conversations.xml

The report has some limitations. To adjust the time range, specify the number of rows or edit the list of Endpoints you’re interested in, you’ll need to modify the SWQL query. Another limitation pertains to the Partner’s hostname. It’s derived from the current DNS table, displaying the DNS name currently associated with the IP address—not the DNS name that was associated with the Partner IP address when the data were reported by the NetFlow Exporter.

If you want to create a report showing Top Endpoints, you can do so by removing the PartnerIP and PartnerHostname columns and eliminating the LEFT JOIN to the DNS table. The adjusted SWQL query would be as follows:

SELECT 
    [N].[Caption] AS ExportingNodeCaption, 
    [F2].[IP],
    [F2].[Hostname],    
    [F2].[Bytes]
FROM
(   
    SELECT TOP 500 
		[F].[NodeID],
        [F].[IP],
        [F].[Hostname],        
        SUM([F].[Bytes]) AS Bytes
    FROM 
        Orion.Netflow.FlowsByIP AS F
    WHERE 
        [F].[IP] IN ('192.168.100.1', '192.168.100.15', '192.168.100.16') OR 
        ([F].[IP] >= '192.168.2.0' AND [F].[IP] <= '192.168.2.255') OR 
        ([F].[IP] >= '192.168.0.0' AND [F].[IP] <= '192.168.0.255')
        AND ([F].[TimeStamp]) > ADDDATE('DAY', -1, GETUTCDATE())
    GROUP BY 
        [F].[NodeID],
        [F].[IP], 
        [F].[Hostname]        
    ORDER BY 
        SUM([F].[Bytes]) DESC
) AS F2
LEFT JOIN Orion.Nodes N ON [N].[NodeID] = [F2].[NodeID]

Deltona

Nice work. It would be awesome to display this by IP Address Groups.