How to View TMG Logs when using SQL Server Express for Logging

Forefront TMG ürünü standart olara firewall ve webproxy loglarını
kurulum ile beraber gelen sql 2008 express edition üzerinde tutmaktadır.
Eğer bir sistem yöneticisi olarak eski tarihli loglara ulaşmak veya
bunların üzerinden bir takım özel raporlar çekmek istiyorsanız eğer SQL
Express in log export özelliğini kullanabilirsiniz

Yapmanız gereken adımlar aşağıdaki gibidir

All Programs / Microsoft SQL Server 2008 / Import and Export Data

Karşılama ekranını geçtikten sonra kaynak seçimini yapıyoruz

Kaynak olarak SQL 2008 Express sürümünü seçtiğimiz zaman üzerindeki veri tabanlarını görebiliyoruz.

Bir sonraki bölüme geçiyoruz

Hedef olarak flat  file seçiyoruz ve dosya için bir yol belirliyoruz. Ayrıca dil ayarlarını ve formatını belirliyoruz.

Bir sonraki sayfa olan "Specify Table Copy or Query page" kısmında herhang bir değişiklik yapmıyoruz.

Daha sonra ise aşağıdaki bölüme geçiyoruz

Kaynak tablo doğru ise column delimiter' i tab olarak seçip ilerliyoruz.

Run Package page sayfasındaki ayarları değiştirmiyoruz ve son noktada çıktıyı alıyoruz

Gelelim bu çıktıyı okumaya, Excel yardımı ile dosyayı açıp sutunlara
ayırabiliriz, bunun için aşağıdaki açıklamalardan yararlanabiliriz

http://msdn.microsoft.com/en-us/library/dd447850.aspx

Ancak buradaki açıklamalara göre excel deki ayırma işlemlerini yapsak
bile aşağıdaki gibi client ip için bir takım görüntüleme sorunları
yaşayabiliriz.

Bu şekilde iplerin okunmuyor olmasının sebebi, TMG nin ipv6 ve ipv4
için sadece bir kolona sahip olması ve iki ip versiyonunun aslında
birbirinden farklı olmasıdır. TMG de bunun tek bir kolon üzerinde tutmak
için dotted-decimal dediğimiz formatta tutmaktadır ve bunun bizim
tarafımızdan çevrilmesi gerekmektedir.

örneğin C0A8019A-ffff-0000-0000-000000000000 kaydı için ip ye dönüşümü aşağıdaki gibi yapabiliriz.

ilk 8 haneyi alalım yani C0A8019A sonra bunları ikişerli gruplar halinde ayıralım

C0 = 192

A8 = 168

01 = 1

9A = 154

192.168.1.154

Bununla tek tek uğraşmamak için aşağıdaki formulü kullanabilirsiniz

=HEX2DEC(MID(A17,2)) &"."&HEX2DEC(MID(A17,3,2))&"."&HEX2DEC(MID(A17,5,2))&"."&HEX2DEC(MID(A17,7,2))

tabiki bunu excel içinde kullanıyoruz 🙂