SQL 쿼리로 패킷 데이터를 조회한다고 ?

패킷데이터를 SQL 쿼리문 형태로 출력할 수 있는 PacketQ 에 대해서 소개해 볼까합니다. 패킷데이터를 간단하고 빠르게 출력해 낼 수 있는 도구입니다. 원래 이 도구가 DNS2db 라는 이름으로 사용되다가 2011년에 이름을 PacketQ 로 변경했습니다. 변경한 이유는 원래의 이름에서 알 수 있듯이 주로 DNS 데이터 중심으로 데이터를 뽑아냈었는데, 다른 프로토콜 까지 확장해 SQL 쿼리문으로 뽑아내고자 하였기 때문입니다. 하지만 아직까지는 자유롭게 처리할 수 있는 것이 DNS 데이터쪽으로 맞춰져 있습니다. 그러므로 DNS 에 한해 쉽게 데이터를 뽑아내고자 하시는 분들한테는 유용할 수 있습니다. DNS 관리자 분들에게는 더욱 유용할수도 있겠습니다.

일단 다운로드 및 세부 정보는 다음 경로에서 확인할 수 있습니다:

https://github.com/dotse/packetq

주요한 기능을 보면 PCAP 파일을 빠르게 파싱할 수 있다는 것을 강점으로 내세우고 있고요 SQL 쿼리문 형태로 사용이 가능합니다. SQL 에 익숙하신 분에게는 더없이 좋겠죠. XML, CSV, JSON, Table 형태로도 데이터를 출력시킬 수 있습니다. 아직은 DNS 와 ICMP 프로토콜 정도만 지원하고 있다는 점입니다. 그리고 PacketQ 가 SQL 쿼리문 형태로 사용할 수 있다고 하지만 완벽한 SQL 문 형태로 지원하는 것은 아닙니다. 조인, 서브쿼리, distinct, like 같은것은 사용할 수 없습니다. 사용가능한 문법은 다음 페이지를 참고해 보시면 됩니다.

https://github.com/dotse/PacketQ/blob/master/src/grammar

간단한 사용방법을 몇개 살펴보겠습니다. SQL 쿼리 같이 * 로 해서 전체 데이터를 다 출력할 수도 있지만 특정필드로도 제한할 수 있습니다. 일단, DNS 데이터에서 추출해 낼 수 있는 데이터 형태는 다음과 같습니다.

      { "name": "id","type": "int" },
      { "name": "s","type": "int" },
      { "name": "us","type": "int" },
      { "name": "ether_type","type": "int" },
      { "name": "src_port","type": "int" },
      { "name": "dst_port","type": "int" },
      { "name": "src_addr","type": "text" },
      { "name": "dst_addr","type": "text" },
      { "name": "protocol","type": "int" },
      { "name": "ip_ttl","type": "int" },
      { "name": "fragments","type": "int" },
      { "name": "qname","type": "text" },
      { "name": "aname","type": "text" },
      { "name": "msg_id","type": "int" },
      { "name": "msg_size","type": "int" },
      { "name": "opcode","type": "int" },
      { "name": "rcode","type": "int" },
      { "name": "extended_rcode","type": "int" },
      { "name": "edns_version","type": "int" },
      { "name": "z","type": "int" },
      { "name": "udp_size","type": "int" },
      { "name": "qd_count","type": "int" },
      { "name": "an_count","type": "int" },
      { "name": "ns_count","type": "int" },
      { "name": "ar_count","type": "int" },
      { "name": "qtype","type": "int" },
      { "name": "qclass","type": "int" },
      { "name": "atype","type": "int" },
      { "name": "aclass","type": "int" },
      { "name": "attl","type": "int" },
      { "name": "aa","type": "bool" },
      { "name": "tc","type": "bool" },
      { "name": "rd","type": "bool" },
      { "name": "cd","type": "bool" },
      { "name": "ra","type": "bool" },
      { "name": "ad","type": "bool" },
      { "name": "do","type": "bool" },
      { "name": "edns0","type": "bool" },
      { "name": "qr","type": "bool" }

이중에서 qtype 과 opcode 를 출력하고 총 3개의 데이터로 제한을 한다면 다음과 같이 쿼리가 만들어질 수 있습니다. -s 뒤에 사용할 쿼리를 넣어주면 됩니다.

# ./packetq -s "select qname,opcode from dns limit 3" test.pcap
[
  {
    "table_name": "result-0",
    "query": "select qname,opcode from dns limit 3",
    "head": [
      { "name": "qname","type": "text" },
      { "name": "opcode","type": "int" }
    ],
    "data": [
      ["n.search.naver.com.",0],
      ["n.search.naver.com.",0],
      ["n.search.naver.com.",0]
    ]
  }
]


어떤 출발지에서 가장 많은 DNS 쿼리 요청을 했는지 출력해 봅니다.

# ./packetq -s "select src_addr,count(*) as count from dns group by src_addr order by count desc limit 1" sample.pcap
[
size = 2
  {
    "table_name": "result-0",
    "query": "select src_addr,count(*) as count from dns group by src_addr order by count desc limit 1",
    "head": [
      { "name": "src_addr","type": "text" },
      { "name": "count","type": "int" }
    ],
    "data": [
      ["172.xx.xx.52",4]
    ]
  }
]


만약 가장 많은 쿼리가 요청된 것을 뽑아낸다면 다음과 같이 합니다. qname 으로 groupby 를 해 줍니다. SQL 에 익숙하신 분들이라면 쿼리만 보고서도 쉽게 이해가 되실 것입니다. 하지만 SQL 경험이 없으신 분들은 group by 가 무엇인지도 혼란스러울 수도 있습니다. 여기서는 SQL 쿼리문 까지는 다루기 힘들기 때문에 의미만을 설명드리겠습니다. qname 으로 그룹을 만들어 주고 각 qname 마다의 카운트를 생성합니다. 그리고 정렬을 count 로 해서 가장 많은 숫자가 처음에 나오게 하고 limit 를 통해 출력데이터를 1개로만 합니다. 그 결과가 아래와 같습니다 :

# ./packetq -s "select qname,count(*) as count from dns group by qname order by count desc limit 1 " test.pcap
[
size = 1513
  {
    "table_name": "result-0",
    "query": "select qname,count(*) as count from dns group by qname order by count desc limit 1 ",
    "head": [
      { "name": "qname","type": "text" },
      { "name": "count","type": "int" }
    ],
    "data": [
      ["resolver3.xxxx[삭제].com.",238]
    ]
  }
]

이번에는 쿼리타입을 출력해 볼까요? 쿼리타입이 기본적으로는 정수 값입니다. 그런데 정수값으로 보면 사람한테는 익숙하지 않습니다. 이때 NAME 펑션 기능을 이용해 쿼리타입(qtype)을 텍스트 형태로 출력시킬 수 있습니다. qtype 으로 그룹을 묶고 타입과, 카운트 정보를 뽑아서 가장 많은 순서대로 출력을 시킵니다.

# ./packetq -s "SELECT NAME( 'qtype' , qtype ) AS qt, COUNT(*) AS antal FROM dns GROUP BY qtype ORDER BY Antal DESC" test.pcap
[
size = 10
  {
    "table_name": "result-0",
    "query": "SELECT NAME( 'qtype' , qtype ) AS qt, COUNT(*) AS antal FROM dns GROUP BY qtype ORDER BY Antal DESC",
    "head": [
      { "name": "qt","type": "text" },
      { "name": "antal","type": "int" }
    ],
    "data": [
      ["A",8626],
      ["AAAA",680],
      ["PTR",566],
      ["TXT",202],
      ["SRV",113],
      ["MX",16],
      ["SOA",8],
      ["0",8],
      ["*",8],
      ["NS",8]
    ]
  }
]

패킷 데이터를 SQL 쿼리문으로 추출해 낼 수 있다는 점에서 굉장히 유용한 부분이 있습니다. 아쉬운 점은 일부 프로토콜로만 한정되어 사용할 수 없지만 광범위하게 프로토콜을 지원한다면 정말 편할거 같은 느낌입니다. 예전에 DNS 프로토콜을 파싱하여 활용할 일이 있어서 찾다가 보게되었는데 나름 유용하게도 이용할 수 있겠다는 생각이 들었습니다.

SQL 쿼리로 패킷데이터를 조회한다는 점, 흥미롭지 않나요.  더 많은 예제는 해당 홈페이지 위키 페이지를 참고하시면 됩니다.

P.S 참고로 ICMP 로 출력할 수 있는 데이터는 다음과 같습니다:

      { "name": "id","type": "int" },

      { "name": "s","type": "int" },

      { "name": "us","type": "int" },

      { "name": "ether_type","type": "int" },

      { "name": "src_port","type": "int" },

      { "name": "dst_port","type": "int" },

      { "name": "src_addr","type": "text" },

      { "name": "dst_addr","type": "text" },

      { "name": "protocol","type": "int" },

      { "name": "ip_ttl","type": "int" },

      { "name": "fragments","type": "int" },

      { "name": "type","type": "int" },

      { "name": "code","type": "int" },

      { "name": "echo_identifier","type": "int" },

      { "name": "echo_sequence","type": "int" },

      { "name": "du_protocol","type": "int" },

      { "name": "du_src_addr","type": "text" },

      { "name": "du_dst_addr","type": "text" },

      { "name": "desc","type": "text" }

아래와 같은 형태로 사용하면 되겠죠. 

./packetq -s "select * from icmp limit 3"