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.

http://www.ip2location.com/

http://www.maxmind.com/app/geolitecity

http://www.wipmania.com/

Ö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.

http://www.bilgiguvenlik.org/2012/03/logparser-sorgu-sonuclarnn-veritabanna.html

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.