StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try this:</p> <pre><code>SELECT T1.SrcAddress, T1.SrcPort, T1.DestAddress, T1.DestPort, T1.Bytes + COALESCE(T2.Bytes, 0) AS TotalBytes, T1.Bytes AS A_to_B, COALESCE(T2.Bytes, 0) AS B_to_A FROM ( SELECT SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes FROM PacketHeaders GROUP BY SrcAddress, SrcPort, DestAddress, DestPort) AS T1 LEFT JOIN ( SELECT SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes FROM PacketHeaders GROUP BY SrcAddress, SrcPort, DestAddress, DestPort) AS T2 ON T1.SrcAddress = T2.DestAddress AND T1.SrcPort = T2.DestPort AND T1.DestAddress = T2.SrcAddress AND T1.DestPort = T2.SrcPort WHERE T1.SrcAddress < T1.DestAddress OR (T1.SrcAddress = T1.DestAddress AND T1.SrcPort = T1.DestPort) OR T2.DestAddress IS NULL </code></pre> <p>On this test data:</p> <pre><code>CREATE TABLE PacketHeaders (ID INT, SrcAddress NVARCHAR(100), SrcPort INT, DestAddress NVARCHAR(100), DestPort INT, Bytes INT); INSERT INTO PacketHeaders (ID, SrcAddress, SrcPort, DestAddress, DestPort, Bytes) VALUES (1, '10.0.25.1', 255, '10.0.25.50', 500, 64), (2, '10.0.25.50', 500, '10.0.25.1', 255, 80), (3, '10.0.25.50', 500, '10.0.25.1', 255, 16), (4, '75.48.0.25', 387, '74.26.9.40', 198, 72), (5, '74.26.9.40', 198, '75.48.0.25', 387, 64), (6, '10.0.25.1', 255, '10.0.25.50', 500, 48), (7, '10.0.25.2', 255, '10.0.25.50', 500, 48), (8, '10.0.25.52', 255, '10.0.25.50', 500, 48); </code></pre> <p>This gives the following results:</p> <pre><code>'10.0.25.1', 255, '10.0.25.50', 500, 208, 112, 96 '10.0.25.2', 255, '10.0.25.50', 500, 48, 48, 0 '10.0.25.52', 255, '10.0.25.50', 500, 48, 48, 0 '74.26.9.40', 198, '75.48.0.25', 387, 136, 64, 72 </code></pre> <p>The way it works is to first group one-way conversations and total the byte counts. This assures that every conversation will be represented exactly twice - once for each direction. This result is then self-joined to give the result you need, filtering the duplicates by enforcing that the (address, port) of A must be less than B. A left join is used to allow one-way conversations.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload