1

$ mergecap -F libpcap -a output-* -w merged.pcap

1
2
3
4
5
6

PCAP_FILE = "/home/victor/work/Projects/24h-Android-Monitoring/pcap/merged.pcap"

# Tshark generated files
DNS_QUERIES  = "/home/victor/work/Projects/24h-Android-Monitoring/pcap/dns_queries.csv"
CONNECTIONS  = "/home/victor/work/Projects/24h-Android-Monitoring/pcap/connections.csv"
HTTP_TRAFFIC = "/home/victor/work/Projects/24h-Android-Monitoring/pcap/http_traffic.csv"

1
2
3
4
5
6
7
8
9

dns_queries = !tshark -r $PCAP_FILE  -R "dns.flags.response == 1"  -E occurrence=f -E header=y \
              -T fields  -e frame.number -e frame.time -e dns.qry.name -e dns.resp.addr > $DNS_QUERIES
    
connections = !tshark -r $PCAP_FILE -E header=y -E separator=\; -T fields -e frame.number \
              -e frame.time -e ip.src -e ip.dst -e tcp.dstport -e frame.protocols > $CONNECTIONS

http_traffic = !tshark -r $PCAP_FILE -Y "http.request" -E header=y -T fields \
              -e frame.number -e frame.time -e ip.dst -e http.request.method -e http.request.uri -e http.user_agent \
              -e http.response.code  -e http.response.phrase -e http.content_length -e data -e text > $HTTP_TRAFFIC

1
2
3

import sqlite3 as sql
con = sql.connect(":memory:")
cur = con.cursor()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

import binascii
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import pandas.io.sql as pdsql 

# Pandas settings
pd.set_option('display.height', 500)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 500)
pd.set_option('display.max_colwidth', 1000)

# Useful functions
def hex2str(s):
    if str(s) == 'nan':
        return None
    else:
        return bytes.fromhex(str(s)).decode('utf-8')
    
# Import DNS queries
dns_df = pd.read_table(DNS_QUERIES)
dns_df.columns = ['frame_number', 'frame_time', 'dns_query', 'dns_response']

# Import connections
con_df = pd.read_table(CONNECTIONS, sep=";")
con_df.columns = ['frame_number', 'frame_time', 'src', 'dst', 'dstport', 'frame_protocols']

# Import http traffic
http_df = pd.read_table(HTTP_TRAFFIC)
http_df.columns = ['frame_number', 'frame_time', 'ip_dst','request_method', 'request_uri', 'user_agent','response_code', 'response_phrase','content_length', 'data', 'text']

# Convert data (hex) to ascii
convert_data = lambda x: hex2str(x)
http_df['data'] = http_df['data'].apply(convert_data)


# Write to SQLite
pdsql.write_frame(dns_df, name="dns", con=con, if_exists="delete")
pdsql.write_frame(con_df, name="connection", con=con, if_exists="delete")
pdsql.write_frame(http_df, name="http", con=con, if_exists="delete")

dns_df.head(5)

#p2 = pdsql.read_frame("""SELECT COUNT(dns_response), dns_query FROM dns""", cnx)
#p2

# Count unique values
#unique_dns = dns_df.groupby('DNS Query')['DNS Response'].nunique().reset_index()
#unique_dns.columns = ['DNS', '# DNS Queries']
#unique_dns.sort(['# DNS Queries'], ascending=False).head(10)

1
2
3
4
5
6
7

p1 = pdsql.read_frame("""
    SELECT COUNT(dns_response) AS '# DNS Responses', dns_query AS 'DNS to lookup' 
    FROM dns GROUP BY dns_query 
    ORDER by 1 DESC
""", con)
#print(p1.head(100).to_string())
p1.head(100)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

p_testing = pdsql.read_frame(""" 
    SELECT COUNT(c.dst),c.dst, c.dstport, c.frame_protocols FROM connection AS c 
    JOIN dns AS d ON c.dst=d.dns_response
    WHERE c.dst == "173.194.70.95" AND c.dst NOT LIKE "10.%"
    ORDER by 1 DESC
""", con)
#p_testing.head(100)

p_testing = pdsql.read_frame("""
    SELECT COUNT(*), (SELECT dns_query FROM dns WHERE dns_response=c.dst LIMIT 1) as DNS, c.dstport, c.frame_protocols FROM connection AS c
    WHERE c.dst NOT LIKE "10.%"
    GROUP by 2
    ORDER by 1 DESC
""", con)
p_testing.head(100)

1
2
3
4
5
6
7
8

p2 = pdsql.read_frame(""" 
    SELECT COUNT(c.dst), d.dns_query, c.dstport, c.frame_protocols FROM connection AS c 
    JOIN dns AS d ON c.dst=d.dns_response
    WHERE c.frame_protocols LIKE "sll:ip:tcp:%"
    GROUP by c.dst
    ORDER by 1 DESC
""", con)
p2.head(100)

1
2
3
4
5
6
7
8
9

p_testing = pdsql.read_frame(""" 
    SELECT  frame_number, frame_protocols
    FROM connection
    WHERE frame_protocols LIKE 'sll:ip:tcp%xml%'
    GROUP by frame_protocols
    ORDER by 1 DESC
""", con)
#print(p1.head(100).to_string())
p_testing.head(100)

1
2
3
4
5
6
7

p_proto = pdsql.read_frame(""" 
    SELECT COUNT(frame_protocols), frame_protocols
    FROM connection
    GROUP by frame_protocols
    ORDER BY 1 DESC
""", con)
p_proto.head(100)

1
2
3
4
5
6
7
8
9

p_ports = pdsql.read_frame(""" 
    SELECT COUNT(c.dstport), c.dstport
    FROM connection AS c
    JOIN dns AS d ON c.dst = d.dns_response
    GROUP by c.dstport
    ORDER by 1 DESC
""", con)
#print(p1.head(100).to_string())
p_ports.head(100)

1
2
3
4
5
6
7
8

p_http_methods = pdsql.read_frame(""" 
    SELECT COUNT(request_method), request_method
    FROM http
    GROUP by request_method
    ORDER by 1 DESC
""", con)
#print(p1.head(100).to_string())
p_http_methods.head(100)

1
2
3
4
5
6
7
8

p_user_agents = pdsql.read_frame(""" 
    SELECT COUNT(user_agent), user_agent
    FROM http
    GROUP by user_agent
    ORDER by 1 DESC
""", con)
#print(p1.head(100).to_string())
p_user_agents.head(100)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

p3 = pdsql.read_frame(""" 
    SELECT h.frame_number, h.ip_dst, d.dns_query, h.request_method, h.request_uri, h.user_agent FROM http AS  h
    JOIN dns AS d ON h.ip_dst = d.dns_response
    WHERE lower(h.request_method) == 'get' AND
        -- Filter all rubish
        (h.request_uri NOT LIKE '%.gif'  AND 
         h.request_uri NOT LIKE '%.jpg'  AND
         h.request_uri NOT LIKE '%.jpeg' AND
         h.request_uri NOT LIKE '%.png'  AND
         h.request_uri NOT LIKE '%.gif'  AND
         h.request_uri NOT LIKE '%.css'  AND 
         h.request_uri NOT LIKE '%.html' AND
         h.request_uri NOT LIKE '%.js') 
    AND
        (d.dns_query NOT LIKE '%amazon%'   AND
         d.dns_query NOT LIKE '%fahrinfo%' AND
         d.dns_query NOT LIKE '%faz.net%'  AND
         d.dns_query NOT LIKE '%heute.de%' AND
         d.dns_query NOT LIKE '%twitter%'  AND
         d.dns_query NOT LIKE '%sueddeutsche%')
    
    GROUP by h.request_uri
    ORDER by d.dns_query
""", con)
p3.head(500)

1
2
3
4
5
6
7

p3 = pdsql.read_frame(""" 
    SELECT h.frame_number, d.dns_query, h.request_uri, h.data, h.text FROM http AS  h
    JOIN dns AS d ON h.ip_dst = d.dns_response
    WHERE lower(h.request_method) == 'post'
    ORDER by h.ip_dst    
""", con)
p3.head(500)