Extracting the raw data of PCAP2JSON can be helpful for debugging and general customized analysis tools. The high level dataflow is described below.
.png?sv=2022-11-02&spr=https&st=2025-05-14T18%3A48%3A48Z&se=2025-05-14T19%3A01%3A48Z&sr=c&sp=r&sig=PuFhH3mYSwCOuctPoYA2zcU5wQoashpyhTF4aUTbAzE%3D "image(90).png")
In many cases it can be helpful to extract JSON data directly out of the Clickhouse Database (5) in the diagram above.
This results in the following additional workflow of (8), shown below.
.png?sv=2022-11-02&spr=https&st=2025-05-14T18%3A48%3A48Z&se=2025-05-14T19%3A01%3A48Z&sr=c&sp=r&sig=PuFhH3mYSwCOuctPoYA2zcU5wQoashpyhTF4aUTbAzE%3D "image(94).png")
Step 1) craft an SQL query
The first step is to create an SQL query with JSON formatting. This is done inside the PCAP2JSON LXC Container.
In the path shown below, create a file pcap2json-export-json.sql as shown below
root@fmadio100v2-228U-pcap2json:~/pcap2json# vi pcap2json-export-json.sqlNOTE: this is running inside the LXC container so there is a green prompt
.png?sv=2022-11-02&spr=https&st=2025-05-14T18%3A48%3A48Z&se=2025-05-14T19%3A01%3A48Z&sr=c&sp=r&sig=PuFhH3mYSwCOuctPoYA2zcU5wQoashpyhTF4aUTbAzE%3D "image(92).png")
Step 2) Full database dump in JSON
The most simple export is without any filtering, paste the contents of the file similar to
select * from pcapflow.meta_1sec limit 100 format JSONEachRowFull list of output formats is found here
https://clickhouse.com/docs/en/interfaces/formats
NOTE: the limt 100 will only output 100 rows, delete this if you want to export the entire database
Step 3) run the SQL script
Command below
cat pcap2json-export-json.sql | clickhouse-client -mnExample output
root@fmadio100v2-228U-pcap2json:~/pcap2json# cat pcap2json-export-json.sql | clickhouse-client -mn <jemalloc>: Number of CPUs detected is not deterministic. Per-CPU arena disabled.
{"timestamp":"2024-11-10 10:00:06","site":"","system":"fmadio100v2-228U","hashHalf":[0,0,0,0,0],"macSrc":"00:0c:29:79:d5:b8","macDst":"f8:f2:1e:bc:cb:61","macProto":"ARP","ipv4Src":"0.0.0.0","ipv4Dst":"0.0.0.0","ipv4Proto":"","ipv4DSCP":"","vlan0":"4080","vlan1":"","mpls0TC":"none","portSrc":"","portDst":"","hostSrc":"0.0.0.0","hostDst":"0.0.0.0","geoipSrc":{"country_name":"","country_iso_code":"\u0000\u0000","longitude":0,"latitude":0,"city_name":"","isp":"","org":"","asn":"0","asn_org":""},"geoipDst":{"country_name":"","country_iso_code":"\u0000\u0000","longitude":0,"latitude":0,"city_name":"","isp":"","org":"","asn":"0","asn_org":""},"flowTypeSrc":"public-internet","flowTypeDst":"public-internet","flowSubTypeSrc":"","flowSubTypeDst":"","deviceSrc":"00:0c:29:79:d5:b8","deviceDst":"sg2-jump0-wg-gw","tapaggDevice":"","tapaggVlan":"4080","tapaggVlanInv":"4081","linkType":"","linkSubType":"","linkSwitchName":"","linkInterface":"","linkDesc":"","linkBandwidth":0,"vlanInner":"","vlanInnerDesc":"","tcpFin":0,"tcpSyn":0,"tcpSynAck":0,"tcpSackPerm":0,"tcpRst":0,"tcpSack":0,"tcpZeroWindow":0,"sumPacket":"1","sumByte":"68","sumFCS":"0","meanPacket":"1","meanByte":"68","meanFCS":"0","maxPacket":"1","maxByte":"68","maxFCS":"0"}
{"timestamp":"2024-11-10 10:00:06","site":"","system":"fmadio100v2-228U","hashHalf":[0,0,0,0,0],"macSrc":"00:0c:29:79:d5:b8","macDst":"f8:f2:1e:bc:cb:61","macProto":"ARP","ipv4Src":"0.0.0.0","ipv4Dst":"0.0.0.0","ipv4Proto":"","ipv4DSCP":"","vlan0":"4080","vlan1":"","mpls0TC":"none","portSrc":"","portDst":"","hostSrc":"0.0.0.0","hostDst":"0.0.0.0","geoipSrc":{"country_name":"","country_iso_code":"\u0000\u0000","longitude":0,"latitude":0,"city_name":"","isp":"","org":"","asn":"0","asn_org":""},"geoipDst":{"country_name":"","country_iso_code":"\u0000\u0000","longitude":0,"latitude":0,"city_name":"","isp":"","org":"","asn":"0","asn_org":""},"flowTypeSrc":"public-internet","flowTypeDst":"public-internet","flowSubTypeSrc":"","flowSubTypeDst":"","deviceSrc":"00:0c:29:79:d5:b8","deviceDst":"sg2-jump0-wg-gw","tapaggDevice":"","tapaggVlan":"4080","tapaggVlanInv":"4081","linkType":"","linkSubType":"","linkSwitchName":"","linkInterface":"","linkDesc":"","linkBandwidth":0,"vlanInner":"","vlanInnerDesc":"","tcpFin":0,"tcpSyn":0,"tcpSynAck":0,"tcpSackPerm":0,"tcpRst":0,"tcpSack":0,"tcpZeroWindow":0,"sumPacket":"1","sumByte":"68","sumFCS":"0","meanPacket":"1","meanByte":"68","meanFCS":"0","maxPacket":"1","maxByte":"68","maxFCS":"0"}
.
.
Or pipe it thru jq for a more human readable format
cat pcap2json-export-json.sql | clickhouse-client -mn | jqExample output
root@fmadio100v2-228U-pcap2json:~/pcap2json# cat pcap2json-export-json.sql | clickhouse-client -mn | jq
{
"timestamp": "2024-11-10 10:00:06",
"site": "",
"system": "fmadio100v2-228U",
"hashHalf": [
0,
0,
0,
0,
0
],
"macSrc": "00:0c:29:79:d5:b8",
"macDst": "f8:f2:1e:bc:cb:61",
"macProto": "ARP",
"ipv4Src": "0.0.0.0",
"ipv4Dst": "0.0.0.0",
"ipv4Proto": "",
"ipv4DSCP": "",
"vlan0": "4080",
"vlan1": "",
"mpls0TC": "none",
"portSrc": "",
"portDst": "",
"hostSrc": "0.0.0.0",
"hostDst": "0.0.0.0",
"geoipSrc": {
"country_name": "",
"country_iso_code": "\u0000\u0000",
"longitude": 0,
"latitude": 0,
"city_name": "",
"isp": "",
"org": "",
"asn": "0",
"asn_org": ""
},
"geoipDst": {
"country_name": "",
"country_iso_code": "\u0000\u0000",
"longitude": 0,
"latitude": 0,
"city_name": "",
"isp": "",
"org": "",
"asn": "0",
"asn_org": ""
},
"flowTypeSrc": "public-internet",
"flowTypeDst": "public-internet",
"flowSubTypeSrc": "",
"flowSubTypeDst": "",
"deviceSrc": "00:0c:29:79:d5:b8",
"deviceDst": "sg2-jump0-wg-gw",
"tapaggDevice": "",
"tapaggVlan": "4080",
"tapaggVlanInv": "4081",
"linkType": "",
"linkSubType": "",
"linkSwitchName": "",
"linkInterface": "",
"linkDesc": "",
"linkBandwidth": 0,
"vlanInner": "",
"vlanInnerDesc": "",
"tcpFin": 0,
"tcpSyn": 0,
"tcpSynAck": 0,
"tcpSackPerm": 0,
"tcpRst": 0,
"tcpSack": 0,
"tcpZeroWindow": 0,
"sumPacket": "1",
"sumByte": "68",
"sumFCS": "0",
"meanPacket": "1",
"meanByte": "68",
"meanFCS": "0",
"maxPacket": "1",
"maxByte": "68",
"maxFCS": "0"
}
.
.