Extract 500k lines from Azure logs and process them against geoip

 

Which countries accessed some.site.tld (10.248.1.1)  last 30 days 

[located in Azure behind Azure Firewall and AzureApplication Gateway]

 

The query:

AzureDiagnostics

| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "ApplicationGatewayFirewallLog"

| project TimeGenerated, clientIp_s, hostname_s //, requestUri_s, action_s, ruleId_s, Message, details_message_s

| where hostname_s contains "some.site.tld"

//| distinct clientIp_s // сортира по различни clientIp_s e.g. uniq ?

//| distinct clientIp_s, TimeGenerated

//| order by hostname_s

//| summarize count() by clientIp_s, bin(TimeGenerated, 5m)

//| summarize count() by clientIp_s, bin(TimeGenerated, 1m)

//| render timechart

 

 

Category == "ApplicationGatewayAccessLog"

Category == "ApplicationGatewayFirewallLog"

 

ResourceProvider

        MICROSOFT.NETWORK

            Resource

        CS-HUB-P-WAF-PUBLIC-AGW

 

Producing more than 30k lines  (restrictions are 10k or 30k max - GUI limitations)

The PowerBI integration for Logs works with Logs API – therefore the limitation is not the UI limitation of 30K results but the API limitation of 500k results

 From <https://learn.microsoft.com/en-us/answers/questions/1328922/query-output-limits-%28laws-pbi%29>

 

Export to excel

Azure  -> logs -> Query -> Run (>30k lines)

• Update query to reduce the number of lines (e.g. where, distinct, etc)
• Export - Power BI ( as an M query) -> will generate and download txt file with API command

Excel -> Data -> Get Data -> From Other Sources -> Blank query => Advanced editor => paste content of txt file (API command with credentials)

The result is 500k lines output based on log query log

 

 

Select only IP addresses and export them to txt/csv file

GeoLite2 data created by MaxMind : https://www.miyuru.lk/geoiplegacy (April 2024)

GeoIP archive (2013) https://mirrors-cdn.liferay.com/geolite.maxmind.com/download/geoip/database/

DB-IP: https://db-ip.com/db/

 

On bash:

apt-get install geoip-bin geoip-database

    MacOS: brew install GeoIP

    dockerfile: dockerfile.run/geoiplookup 

cat ./ip_list.txt | sort | uniq | xargs -n 1 geoiplookup { }

 

Or running the script bellow:

cat geoip_lookup.sh

#!/usr/bin/bash

#cat /var/log/apache2/ | awk '{print $1}' > ips.txt

#uniq ips.txt > uniques.txt

IPS=`cat IPs.txt`

#echo $IPS

for ip in $IPS

do

echo "`geoiplookup $ip | cut -d "," -f2 | sed -e 's/^[ \t]*//'` `geoiplookup -f /usr/share/GeoIP/GeoLiteCity.dat $ip | cut -d "," -f4,5 | sed -e 's/^[ \t]*//'` $ip"

#echo

done

Will provide a result like:

Netherlands Noord-Holland, Amsterdam 52.142.230.106

United States N/A, Boydton 52.167.144.137

Germany N/A, N/A 194.37.84.209

Sweden Sodermanlands Lan, Nyköping 93.158.90.70

 

 

MaxMind Country:

wget --no-check-certificate https://dl.miyuru.lk/geoip/maxmind/country/maxmind4.dat.gz

MaxMind City:

wget --no-check-certificate https://dl.miyuru.lk/geoip/maxmind/city/maxmind4.dat.gz