How to match IP addresses to a Subnets and get SUM

Can you help me with this SELECT in Clickhouse ?

I want to measure the traffic accounting from some networks. I am using clickhouse db with 2 tables:

select * from network_account_db  

┌─network───────----─┬─source─┬─category─┐
│ 192.168.200.0/29   │ server │ general  │
│ 192.168.200.11/30  │ server │ general  │
│ 192.168.200.22/32  │ server │ general  │
└───────────────----─┴────────┴──────────┘

 select  packetDate,packetDateTime,sampleRatio,srcIp,dstIp,length from traffic  
 
┌─packetDate─┬──────packetDateTime─┬─sampleRatio─┬─────srcIp─┬──────dstIp─┬─length─┐
│ 2021-02-04 │ 2021-02-04 22:15:20 │           1 │ 232998210 │  767413237 │   1280 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │  918211986 │     40 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1150185814 │  30088 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1168387235 │     52 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1169107244 │    104 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1169107244 │     52 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1224157376 │    617 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1476066034 │   1425 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1600411769 │   4656 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1743465996 │     52 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1746016762 │    108 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 1746284673 │    901 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 3194642526 │   1976 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 2315259109 │   2403 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 2540034693 │     52 │
│ 2021-02-04 │ 2021-02-04 22:15:19 │           1 │ 767413237 │ 2540034693 │     52 │

I want to measure the traffic something like

select sum(length * sampleRatio ) AS total, category  
from ( select network as net from network_account_db where source='server'   ) 
where  srcIp=IPv4StringToNum(net)

I need to get, for example:

category=general
total=242422

Can you help me to get the correct SELECT. I have a table with network and another with IP.



Read more here: https://stackoverflow.com/questions/66054802/how-to-match-ip-addresses-to-a-subnets-and-get-sum

Content Attribution

This content was originally published by maxid at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: