notebook.community

Edit and run



In [1]:

    
import pyshark
import pandas as pd



In [2]:

    
# Read packet capture and set display filter to only retrieve TDS traffic
cap = pyshark.FileCapture("ms-sql-tds-rpc-requests.pcap",display_filter="tds")



In [3]:

    
tds_types = {
    "1": "SQL Batch",
    "2": "Pre-TDS7 Login",
    "3": "RPC",
    "4": "Tabular result",
    "5": "unused",
    "6": "Attention signal",
    "7": "Bulk load data",
    "8": "Federated authentication token",
    "9": "unused",
    "10": "unused",
    "11": "unused",
    "12": "unused",
    "13": "unused",
    "14": "Transaction manager request",
    "15": "unused",
    "16": "TDS7 Login",
    "17": "SSPI",
    "18": "Pre-login"
}

procedure_codes = {
    "1": "Sp_Cursor",
    "2": "Sp_CursorOpen",
    "3": "Sp_CursorPrepare",
    "4": "Sp_CursorExecute",
    "5": "Sp_CursorPrepExec",
    "6": "Sp_CursorUnprepare",
    "7": "Sp_CursorFetch",
    "8": "Sp_CursorOption",
    "9": "Sp_CursorClose",
    "10": "Sp_ExecuteSql",
    "11": "Sp_Prepare",
    "12": "Sp_Execute",
    "13": "Sp_PrepExec",
    "14": "Sp_PrepExecRpc",
    "15": "Sp_Unprepare"
}

cap_data = []
for pkt in cap:
    pkt_data = {}
    try:
        pkt_data["ts"] = pkt.sniff_timestamp
        pkt_data["src_ip"] = str(pkt.ip.src)
        pkt_data["dst_ip"] = str(pkt.ip.dst)
        pkt_data["src_port"] = int(pkt.tcp.srcport)
        pkt_data["dst_port"] = int(pkt.tcp.dstport)
        pkt_data["tds_type"] = int(pkt.tds.type)
        pkt_data["tds_type_str"] = str(tds_types.get(pkt.tds.type))
        if pkt_data["tds_type"] == 1:
            # This is a SQL batch type
            pkt_data["query"] = str(pkt.tds.query)
        elif pkt_data["tds_type"] == 3:
            # This is a remote procedure call
            # Look for a procedure ID
            try:
                if procedure_codes.get(pkt.tds.rpc_proc_id) is not None:
                    pkt_data["rpc_proc_id"] = procedure_codes.get(pkt.tds.rpc_proc_id)
                else:
                    pkt_data["rpc_proc_id"] = int(pkt.tds.rpc_proc_id)
            except:
                pass
            # Look for a procedure name
            try:
                pkt_data["rpc_proc_name"] = str(pkt.tds.rpc_name)
            except:
                pass
        else:
            # Not a RPC or query
            pass
        cap_data.append(pkt_data)
    except Exception as e:
        print("Exception: " + str(e))



In [4]:

    
# Convert the data into a pandas dataframe
df = pd.DataFrame(cap_data).fillna(value="")
#df
df[['ts','src_ip','src_port','dst_ip','dst_port','tds_type','tds_type_str','query','rpc_proc_id','rpc_proc_name']]









    Out[4]:







  
    
      
      ts
      src_ip
      src_port
      dst_ip
      dst_port
      tds_type
      tds_type_str
      query
      rpc_proc_id
      rpc_proc_name
    
  
  
    
      0
      1240877917.888015000
      10.111.111.111
      1111
      10.0.0.1
      1433
      1
      SQL Batch
      set transaction isolation level  read committ...
      
      
    
    
      1
      1240877917.888358000
      10.0.0.1
      1433
      10.111.111.111
      1111
      4
      Tabular result
      
      
      
    
    
      2
      1240877917.918653000
      10.111.111.111
      1111
      10.0.0.1
      1433
      3
      RPC
      
      Sp_PrepExec
      
    
    
      3
      1240877918.029044000
      10.0.0.1
      1433
      10.111.111.111
      1111
      4
      Tabular result
      
      
      
    
    
      4
      1259762400.004437000
      10.111.111.111
      2222
      10.0.0.1
      1433
      1
      SQL Batch
      COMMIT TRANSACTION
      
      
    
    
      5
      1259762400.004540000
      10.0.0.1
      1433
      10.111.111.111
      2222
      4
      Tabular result
      
      
      
    
    
      6
      1259762400.022561000
      10.111.111.111
      3333
      10.0.0.1
      1433
      3
      RPC
      
      
      p_GetBogusData
    
    
      7
      1259762400.033701000
      10.111.111.111
      4444
      10.0.0.1
      1433
      3
      RPC
      
      
      sp_executesql
    
    
      8
      1259762400.716204000
      10.111.111.111
      5555
      10.0.0.1
      1433
      1
      SQL Batch
      set transaction isolation level  read committ...
      
      
    
    
      9
      1259762400.730846000
      10.0.0.1
      1433
      10.111.111.111
      5555
      4
      Tabular result
      
      
      
    
    
      10
      1259762400.747372000
      10.111.111.111
      5555
      10.0.0.1
      1433
      3
      RPC
      
      Sp_PrepExec
      
    
    
      11
      1259762401.224921000
      10.0.0.1
      1433
      10.111.111.111
      5555
      4
      Tabular result
      
      
      
    
    
      12
      1259762401.226157000
      10.111.111.111
      5555
      10.0.0.1
      1433
      3
      RPC
      
      Sp_PrepExec
      
    
    
      13
      1259762401.697819000
      10.0.0.1
      1433
      10.111.111.111
      5555
      4
      Tabular result
      
      
      
    
    
      14
      1259762401.711921000
      10.111.111.111
      5555
      10.0.0.1
      1433
      3
      RPC
      
      Sp_Execute
      
    
    
      15
      1259762401.726686000
      10.0.0.1
      1433
      10.111.111.111
      5555
      4
      Tabular result
      
      
      
    
    
      16
      1259762403.727473000
      10.111.111.111
      5555
      10.0.0.1
      1433
      3
      RPC
      
      Sp_PrepExec
      
    
    
      17
      1259762403.809000000
      10.0.0.1
      1433
      10.111.111.111
      5555
      4
      Tabular result
      
      
      
    
    
      18
      1259762405.827627000
      10.111.111.111
      5555
      10.0.0.1
      1433
      3
      RPC
      
      Sp_Execute
      
    
    
      19
      1259762405.864112000
      10.0.0.1
      1433
      10.111.111.111
      5555
      4
      Tabular result
      
      
      
    
    
      20
      1259762407.864719000
      10.111.111.111
      5555
      10.0.0.1
      1433
      3
      RPC
      
      Sp_PrepExec
      
    
    
      21
      1259762407.935765000
      10.0.0.1
      1433
      10.111.111.111
      5555
      4
      Tabular result
      
      
      
    
    
      22
      1259762474.884680000
      10.111.111.111
      6666
      10.0.0.1
      1433
      3
      RPC
      
      
      
    
    
      23
      1259762474.884730000
      10.111.111.111
      6666
      10.0.0.1
      1433
      3
      RPC
      
      
      p_SaveExample
    
    
      24
      1259762477.536189000
      10.111.111.111
      7777
      10.0.0.1
      1433
      3
      RPC
      
      
      p_SetBogusSample
    
    
      25
      1259762482.456090000
      10.111.111.111
      8888
      10.0.0.1
      1433
      3
      RPC
      
      
      p_GetMyExampleTableRowCount
    
    
      26
      1278068444.584977000
      10.111.111.111
      9999
      10.0.0.1
      1433
      3
      RPC
      
      
      proc_GetMyExampleTableSampleMetaData
    
    
      27
      1278068444.614485000
      10.111.111.111
      11111
      10.0.0.1
      1433
      3
      RPC
      
      
      proc_GetMyExampleTableSampleMetaData
    
    
      28
      1278068444.650715000
      10.111.111.111
      22222
      10.0.0.1
      1433
      3
      RPC
      
      
      proc_FetchMyExampleData
    
    
      29
      1278068444.666075000
      10.111.111.111
      33333
      10.0.0.1
      1433
      3
      RPC
      
      
      dbo.proc_GetMySampleDataItems



In [5]:

    
# Output and count all queries in the dataset
df['query'].value_counts()









    Out[5]:





                                                                                    27
 set transaction isolation level  read committed  set implicit_transactions off      2
COMMIT TRANSACTION                                                                   1
Name: query, dtype: int64



In [6]:

    
# Look at named remote procedure call functions called
df['rpc_proc_name'].value_counts()









    Out[6]:





                                        21
proc_GetMyExampleTableSampleMetaData     2
p_GetBogusData                           1
p_GetMyExampleTableRowCount              1
sp_executesql                            1
proc_FetchMyExampleData                  1
p_SaveExample                            1
p_SetBogusSample                         1
dbo.proc_GetMySampleDataItems            1
Name: rpc_proc_name, dtype: int64



In [7]:

    
# Look at remote procedure call function values
df['rpc_proc_id'].value_counts()









    Out[7]:





               23
Sp_PrepExec     5
Sp_Execute      2
Name: rpc_proc_id, dtype: int64



In [8]:

    
df.loc[df['rpc_proc_name'] == "p_GetBogusData"][['ts','src_ip','dst_ip','rpc_proc_name']]









    Out[8]:







  
    
      
      ts
      src_ip
      dst_ip
      rpc_proc_name
    
  
  
    
      6
      1259762400.022561000
      10.111.111.111
      10.0.0.1
      p_GetBogusData



In [9]:

    
df2 = df.loc[(df['rpc_proc_name'] == "p_SetBogusSample") | (df['rpc_proc_name'] == "sp_executesql")]
df2[['ts','src_ip','dst_ip','rpc_proc_name']]









    Out[9]:







  
    
      
      ts
      src_ip
      dst_ip
      rpc_proc_name
    
  
  
    
      7
      1259762400.033701000
      10.111.111.111
      10.0.0.1
      sp_executesql
    
    
      24
      1259762477.536189000
      10.111.111.111
      10.0.0.1
      p_SetBogusSample

	ts	src_ip	src_port	dst_ip	dst_port	tds_type	tds_type_str	query	rpc_proc_id	rpc_proc_name
0	1240877917.888015000	10.111.111.111	1111	10.0.0.1	1433	1	SQL Batch	set transaction isolation level read committ...
1	1240877917.888358000	10.0.0.1	1433	10.111.111.111	1111	4	Tabular result
2	1240877917.918653000	10.111.111.111	1111	10.0.0.1	1433	3	RPC		Sp_PrepExec
3	1240877918.029044000	10.0.0.1	1433	10.111.111.111	1111	4	Tabular result
4	1259762400.004437000	10.111.111.111	2222	10.0.0.1	1433	1	SQL Batch	COMMIT TRANSACTION
5	1259762400.004540000	10.0.0.1	1433	10.111.111.111	2222	4	Tabular result
6	1259762400.022561000	10.111.111.111	3333	10.0.0.1	1433	3	RPC			p_GetBogusData
7	1259762400.033701000	10.111.111.111	4444	10.0.0.1	1433	3	RPC			sp_executesql
8	1259762400.716204000	10.111.111.111	5555	10.0.0.1	1433	1	SQL Batch	set transaction isolation level read committ...
9	1259762400.730846000	10.0.0.1	1433	10.111.111.111	5555	4	Tabular result
10	1259762400.747372000	10.111.111.111	5555	10.0.0.1	1433	3	RPC		Sp_PrepExec
11	1259762401.224921000	10.0.0.1	1433	10.111.111.111	5555	4	Tabular result
12	1259762401.226157000	10.111.111.111	5555	10.0.0.1	1433	3	RPC		Sp_PrepExec
13	1259762401.697819000	10.0.0.1	1433	10.111.111.111	5555	4	Tabular result
14	1259762401.711921000	10.111.111.111	5555	10.0.0.1	1433	3	RPC		Sp_Execute
15	1259762401.726686000	10.0.0.1	1433	10.111.111.111	5555	4	Tabular result
16	1259762403.727473000	10.111.111.111	5555	10.0.0.1	1433	3	RPC		Sp_PrepExec
17	1259762403.809000000	10.0.0.1	1433	10.111.111.111	5555	4	Tabular result
18	1259762405.827627000	10.111.111.111	5555	10.0.0.1	1433	3	RPC		Sp_Execute
19	1259762405.864112000	10.0.0.1	1433	10.111.111.111	5555	4	Tabular result
20	1259762407.864719000	10.111.111.111	5555	10.0.0.1	1433	3	RPC		Sp_PrepExec
21	1259762407.935765000	10.0.0.1	1433	10.111.111.111	5555	4	Tabular result
22	1259762474.884680000	10.111.111.111	6666	10.0.0.1	1433	3	RPC
23	1259762474.884730000	10.111.111.111	6666	10.0.0.1	1433	3	RPC			p_SaveExample
24	1259762477.536189000	10.111.111.111	7777	10.0.0.1	1433	3	RPC			p_SetBogusSample
25	1259762482.456090000	10.111.111.111	8888	10.0.0.1	1433	3	RPC			p_GetMyExampleTableRowCount
26	1278068444.584977000	10.111.111.111	9999	10.0.0.1	1433	3	RPC			proc_GetMyExampleTableSampleMetaData
27	1278068444.614485000	10.111.111.111	11111	10.0.0.1	1433	3	RPC			proc_GetMyExampleTableSampleMetaData
28	1278068444.650715000	10.111.111.111	22222	10.0.0.1	1433	3	RPC			proc_FetchMyExampleData
29	1278068444.666075000	10.111.111.111	33333	10.0.0.1	1433	3	RPC			dbo.proc_GetMySampleDataItems