Geolocation verilerinin Log
Yönetimi söz konusu olduğunda önemi büyüktür. Bir çok logun
anlamlandırılmasında ve işlenmesinde geolocation verilerinin kullanılması
logları epey anlamlı hale getirir. Bugün bir çok firma ürünleri ile geolocation
verilerini entegre bir şekilde kullanmaktadır. Log yönetim sistemlerinde erişim
IP adresleri ülkesel bazda raporlanabilmektedir. Firewall ve IPS sistemleri
üzerinde ülkesel bazda erişim kuralları tanımlamak mümkündür. Ayrıca internet
bankacılığı erişimlerinde ülkesel ve ISP bazında yapılan kısıtlamalar için yine
geolocation verileri kulanılmaktadır.
Amacımız çok basit. Geolocation verilerini Logparser
ile veritabanına atacağız. Sonrasında veritabanı üzerinde tutulan tüm loglar
için IP adreslerinin ülkesel karşılıklarını bulmak ve raporlamak için kullanılabilecek bir fonksiyon geliştireceğiz.
Notlar
Adım 1: Geolocation Verilerinin İndirilmesi
Geolocation verilerini elde edebileceğiniz ücretli ve ücretli bir çok ürün sunulmaktadır. İhtiyaca göre IP adresi bilgilerinin sadece ülkesel karşılıklarını elde edebileceğiniz gibi ISP, şehir ve diğer detaylarını sunan da ürünler vardır. Ücretli ürünler arasında IP2Location ürünü gayet başarılı bir üründür. Ücretsiz ürünler arasında ise GeoLite ve WIPmania sayılabilir. Ücretli ürünler güncellemelerin kritik olduğu ve IP adreslerine ait daha fazla detay gerektiği durumlarda tercih edilebilir.
Öncelikle WIPmania sitesinden ücretsiz geolocation veritabanını text formatında indiriyoruz.
Adım 2: Verilerin Formatının Kontrol Edilmesi
İndirdiğimiz dosyanın içeriğini
textline formatı ile sorgulayarak kontrol ettiğimizde standart bir csv dosyası
olduğunu görebiliyoruz. Ayrıca bir Header satırı yok. Şimdi bu verileri
veritabanına atmaya hazırız.
Adım 3: System DSN Oluşturulması
Verilerin veritabanına atılması
için ilk adım DSN oluşturulmasıdır. Bu örnekte sorguyu çalıştırdığımız
kullanıcının veritabanı üzerinde yazma ve tablo oluşturma hakkı var. Bu yetkilere
sahip bir sql kullanıcı ile de aynı işlemler yapılabilir.
Adım 4: Geolocation Tablosunun Oluşturulması
Ben işleri basitleştirmek için
tablonun create edilmesi işlemini logparser uygulamasına yaptırıyorum.
Logparser ile verilerin veritabanına yazılması aşamasında dikkat dilecek en
önemli noktalardan birisi tablo üzerindeki alanlarla verilerin tiplerinin
birebir uyuşmasıdır. Tabloyu Logparser uygulamasının create etmesi ile
olabilecek sorunların önüne geçmek açısından önemli. Sonrasında tabloyu
istediğim şekilde düzenliyorum. Eğer tabloyu önden oluşturursak DSN
oluştururken kullandığımız kullanıcının sadece tabloya yazma yetkisi olması
yeterlidir.
Adım 5: Verilerin Veritabanına Aktarılması
Şimdi tüm geolocation verilerini
veritabanına aktarmaya hazırız. Aşağıdaki sorguyu q1.sql dosyasına kaydedip
aşağıdaki komutu çalıştırdığımızda verilerimizi veritabanına atmış oluyoruz.
Sorgu:
Select Field1,
Field2, Field3, Field4, Field5, Field6
Into
geolocation_table
From worldip*
Komut:
Logparser file:q1.sql –i:csv
–headerrow:off –o:sql –server:Localhost –database:TestLogDB –dsn:Dsn_TestLogDB -ignoreIdCols:ON
Adım 6: Verilerin
İşlenmesi için SQL Function Oluşturulması
Aşağıda verilerin
işlenmesi için oluşturulmuş iki fonksiyon yer almaktadır. Birincisi IP adresini
IP numarasına çevirmektedir. İkincisi ise yeni oluşturduğumuz tablo üzerinden
sorgulama yaparak IP adresinin ülkesel karşılığını bulmaktadır.
/* UserDefinedFunction to convert
IP Address to IP Number*/
CREATE FUNCTION [dbo].[IPAddressToInteger] (@IP AS varchar(15))
RETURNS bigint
AS
BEGIN
RETURN (CONVERT(bigint, PARSENAME(@IP,1)) +
CONVERT(bigint, PARSENAME(@IP,2)) * 256 +
CONVERT(bigint, PARSENAME(@IP,3)) * 65536 +
CONVERT(bigint, PARSENAME(@IP,4)) * 16777216)
END
GO
/* UserDefinedFunction to find
Country Code*/
CREATE FUNCTION [dbo].[FindCountryCode](
@IP AS
varchar(30))
RETURNS varchar(2)
AS
BEGIN
DECLARE
@CountryCode varchar(2)
declare @IPNum bigint
set @IPNum = dbo.IPAddressToInteger(@IP)
select
@CountryCode = CountryCode
from dbo.geolocation_table
where
StartIPNumber < @IPNum
and EndIPNumber
> @IPNum
Return
@CountryCode
END
Sonuç
Bu geolocation verilerinin
veritabanına aktarılması için basit bir yöntem. Yöntem ihtiyaçlara göre veya
kullanmayı tercih ettiğiniz veritabanının yapısına göre şekillenecektir. Birçok
veritabanı csv formatında tutulmaktadır ve basit bir kaç değişiklikle bu yöntem
kullanılabilir.
Veritabanı üzerinde tutulmayan loglar için de geolocation verilerinin kullanılması için yöntemler var. Bence en kolayı logları öncelikle veritabanına atıp sonrasında bu loglara geolocation bilgilerinin eklenmesidir. Text olarak tutulan logların veritabanına atılması ile ilgili bir aşağıda bir makale bulabilirsiniz. Logları veritabanına atmadan Logparser ile geolocation verilerinin eklenmesi de mümkündür. Ancak birazık daha zor bir yöntem.
Geolocation verileri bir çok
logun anlamlandırılmasında ve yorumlanmasında işlerinizi epey
kolaylaştıracaktır. Özellikle IPS sistemleri üzerinde atak loglarını ve web
sitesine erişim loglarını yorumlarken geolocation verileri çok anlamlıdır. Erişimlerin IP adreslerinin ülkesel karşılığı ve ISP bilgisi, güvenlik olaylarını belirlemede, yorumlamada ve alıncak aksiyonların belirlenmesinde çok önemli bir rol oynayacaktır.
Muhteşemsin :)
YanıtlayınSil