Python Programming
Export Tables from Access to PostgreSQL
This article describes how to export a table from Access DB to postgresql via excel and python.
March 14, 2023
No Comments
Menu
- mukiraz@mukiraz.com
In the previous section, we simulated the flooding attack, the decreased rank attack, and the version number increase attack with contiki cooja. We had performed these simulations with both benign and malicious motes. A total of 6 raw data sets emerged from the simulations.
The raw dataset has the following columns:
No : Row number
Time : Execution time (ms)
Source : Source IP address (IPV6)
Destination: Destination IP address (IPV6)
Protocol : Protocol
Length : Packet length
Info : Bilgi (DIO, DIS, DAO, Ack messages)
The information obtained from the raw data set will not be enough to apply machine learning. The raw data obtained from simulations containing weak nodes is completely different from the raw data obtained from simulations containing normal motes. It has been observed that this difference is the number of packets, message types, total packet lengths and rates. To detect this anomaly, the raw data is divided into 1-second frames. Within frames of each second, the following values were calculated, and a new data set was created.
The creation of the new dataset was by means of the following pseudocode.
START
Dset=INPUT(RawDataset)
WHILE Dset Rows Ends
Duration=time(current_row)-time(previous_row)
Duration_list=APPEND(Duration)
ENDWHILE
Dset = Dset + Duration_list
IP_dictionary={IP_Adress :unique_number}
Crr_scnd=60
Counter=0
fs=FLOOR(Dset[Duration_list])
WHILE counter < frame_second
osf= GET(Dset[Time]>= fs and Dset[Time]<= Crr_scnd+1)
WHILE osf Rows Ends:
Osf_list=[ src=IP_dictionary[Source IP_Adress],
dst=IP_dictionary[Dest. IP_Adress],
pct_cnt=COUNT(rows)
src_mote_rt= COUNT(src)/pct_cnt
dst_mote_rt= COUNT(dst)/pct_cnt
src_mote_dur=SUM(src_duration)
dst_mote_dur= SUM(dst_duration)
ttal_pckt_dur= SUM(duration)
ttal_pckt_lngth= SUM(pckt_lngth)
src_pckt_rt= SUM(src_pckt_lngth)/ ttal_pckt_lngth
dst_pckt_rt= SUM(dst_pckt_lngth)/ ttal_pckt_lngth
dio_msg_cnt= COUNT(dio_messages)
dis_msg_cnt= COUNT(dis_messages)
dao_msg_cnt= COUNT(dao_messages)
other_msg_cnt= COUNT(other_messages)
IF Dset=”Normal”
Label=0
ELSE
Label=1
ENDIF
ENDWHILE
New_dset=APPEND(Osf_list)
ENDWHILE
END
An example of the newly created dataset is in the table below.
This article describes how to export a table from Access DB to postgresql via excel and python.
In this blog post, the Flooding Attack, Decreased Rank Attack and Version Number Increase Attack in the RPL protocol were trained and detected by “Decision Tree”, “Logistic Regression”, “Random Forest”, “Naive Bayes”, “K Nearest Neighbor” and “Artificial Neural Networks” algorithms.
The test results for the attacks were compared, as a result of the comparison, the Artificial Neural Networks algorithm with an accuracy rate of 97.2% in the detection of Flooding Attacks, the K Nearest Neighbor algorithm with an accuracy rate of 81% in the detection of Version Number Increase Attacks, and the Artificial Neural Networks with an accuracy rate of 58% in the detection of Decreased Rank attacks algorithm has been found to show success.
I continue to share how I did my master’s thesis titled Comparison of Machine Learning Algorithms for the Detection of Vulnerability of RPL-Based IoT Devices, my experiences in this process, and the codes in this thesis in a series of articles on my blog.
So far, I have provided detailed information about the RPL protocol and the attacks that take place in the RPL protocol. Then, I experimented with Flooding Attacks, Version Number Increased Attack, and Decreased Rank Attack, extracting the raw data and making sense of that raw data. I compared the results of experiments with weak knots with statistical methods.
In this section, I will interpret the numerical results of the attacks we detect with machine learning algorithms.