14 Nisan 2012 Cumartesi

Logparser ile Geolocation Verilerinin Veritabanına Atılması


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.

Aşağıda işlem adımlarını içeren bir video ve kısa kısa açıklamalarını bulabilirsiniz.







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. 



1 yorum: