Analysis of SSH Honeypot Data with PowerBI
I have been experimenting for a while with Microsoft PowerBI (1) using honeypot data, parsing it into comma delimited (CSV), sorting this data in a way that is easier to manipulate and visualize.
For this report, I used my SSH honeypot logs of 2022 to view the top scanner and the ownership of each IP by AS Network and country code.
Using regular expression, I used the following command using Linux pipe (|) to parse the honeypot logs into a file containing the date, IP address, SSH type use and add 1:
grep SSH tcp-honeypot-20220703-114801.log |sed 's/\\n.*//g' | sed 's/\\r.*//g' | sed 's/\([[:digit:]]\{,8\}\)-.*: 192.168.25.9:[[:digit:]]\{1,5\}-\([[:digit:]].*\):[[:digit:]]\{1,5\} data .\(SSH.*\)/\1,\2,\3/g' | grep -v Python | sed '1i\Date,IP,SSH,Total' | sed 's/\([0-9]\{4\}\)\([0-9]\{2\}\)\([0-9]\{2\}\)\(,.*\)/\1-\2-\3\4,1/g' > /tmp/ssh_activity.csv
Sample Output
Date,IP,SSH,Total
2022-01-16,92.255.85.237,SSH-2.0-libssh_0.9.6,1
2022-01-16,112.85.42.28,SSH-2.0-PUTTY,1
2022-01-16,212.6.50.21,SSH-2.0-libssh-0.6.3,1
2022-01-16,122.194.229.45,SSH-2.0-PUTTY,1
Next step was to get some additional information on each IP, its AS Network and country location. My script used the following command to get the data from the database file:
mmdblookup --file /usr/share/GeoIP/GeoLite2-Country.mmdb --ip $IP country iso_code
mmdblookup --file /usr/share/GeoIP/GeoLite2-ASN.mmdb --ip $IP autonomous_system_number
Sample Output
IP,Country,ASN
99.97.212.80,US,7018
99.96.122.99,US,7018
99.61.199.183,US,7018
99.43.32.83,US,7018
A copy of the parsing script is available here. The script read the IP address and query the 2 database files to build a list (.csv) that can be imported into PowerBI.
After completing these steps, import both files into PowerBI. Last step is to confirm Manage relationships between the two files using the IP address field. Select Model and Manage relationships and confirm the IP address are linked together:
Now I can proceed to build the visualization I need to see various statistics of the SSH activity since the beginning of January 2022. This first picture the overall activity of the two CSV files imported into PowerBI:
The above picture depicts all the logs imported into the tool. At this point, anything selected will automatically refresh all the graphs to show the activity selected. This next selection shows the top SSH banner captured over the past 7 months (SSH-2.0-libssh2_1.4.3):
I have SSH logs going back to 2018 and this last selection got me curious because it is the first time I have ever seen this SSH banner (HELLOWORLD) starting this month.
When any of the data is selected, this tool automatically refreshes the data and the Total to match the selection. It is a quick and easy way to analyze any parts of the data stored into PowerBI. PowerBI support many data format including databases, Excel, CSV, text, Web, cloud, etc.
If you want to try out PowerBI, I have shared a copy of the logs from this diary which can be open into a PowerBI available for download here.
[1] https://powerbi.microsoft.com/en-us/downloads/
[2] https://handlers.sans.edu/gbruneau/scripts/process_geoip.sh
[3] https://installati.one/centos/7/libmaxminddb-devel/
[4] https://maxmind.github.io/libmaxminddb/mmdblookup.html
[5] https://gist.github.com/reanimat0r/dd0ef5005f307d781c622ac446770c13
[6] https://handlers.sans.edu/gbruneau/scripts/diary_logs.pbix
-----------
Guy Bruneau IPSS Inc.
My Handler Page
Twitter: GuyBruneau
gbruneau at isc dot sans dot edu
Comments
www
Nov 17th 2022
6 months ago
EEW
Nov 17th 2022
6 months ago
qwq
Nov 17th 2022
6 months ago
mashood
Nov 17th 2022
6 months ago
isc.sans.edu
Nov 23rd 2022
6 months ago
isc.sans.edu
Nov 23rd 2022
6 months ago
isc.sans.edu
Dec 3rd 2022
5 months ago
isc.sans.edu
Dec 3rd 2022
5 months ago
<a hreaf="https://technolytical.com/">the social network</a> is described as follows because they respect your privacy and keep your data secure. The social networks are not interested in collecting data about you. They don't care about what you're doing, or what you like. They don't want to know who you talk to, or where you go.
<a hreaf="https://technolytical.com/">the social network</a> is not interested in collecting data about you. They don't care about what you're doing, or what you like. They don't want to know who you talk to, or where you go. The social networks only collect the minimum amount of information required for the service that they provide. Your personal information is kept private, and is never shared with other companies without your permission
isc.sans.edu
Dec 26th 2022
5 months ago
isc.sans.edu
Dec 26th 2022
5 months ago