Udemy’de Yeni Kursum, “Devexpress Dashboard ile İş Zekası ve Veri Analitiği”

Merhaba arkadaşlar,
Udemy’de yeni kursum sonunda yayına alındı. Önceki kursumuzda veriyi yönetmeyi öğrenmiştik bu kez veriyi analiz etmeyi öğreneceğiz.

Bu kursla birlikte Bilişim sektöründe çalışan herkesin ihtiyacı olan veriyi görsel ve analitik olarak inceleme ve değerlendirme noktasında ihtiyacımız olan İş Zekası uygulamalarına çok düşük bir bütçe ile sahip olabileceksiniz.

Dakikalar içerisinde chart’lar, grafikler, gauge’lar, özet tablolar, haritalar içeren dashboardlar ve çok daha fazlasını oluşturabileceksiniz.

Oluşturduğunuz bu dashboardları hem windows, hem web ve hem de mobilde görüntüleyebileceksiniz.

Üstelik öğrenmek için sadece bu kursu almanız yeterli.

7 saat ile başlayan kursum  yeni dersler, yeni veritabanları ve yeni örnekler eklenerek 11 saat oldu. İnşallah yeni eklemeler ile de yaşayan bir kurs olmaya devam edecek.

Aşağıda örnekler ile açıkladığım bu kursumu 24,90 TL’ye bu linkten hemen alabilirsiniz.

https://www.udemy.com/devexpress-dashboard-ile-is-zekasi/?couponCode=FREECPNDXWP

İncelediğiniz zaman zaten seveceğinizi düşündüğüm bu kursta veriyi keyif alarak, sorgulayarak, sözel sorulara sayısal ve görsel cevaplar arayarak analiz etmeyi öğretmeyi hedefledim.

Bu işi daha da eğlenceli hale getirmek adına da gerçek hayattan gerçek örnekler ile  çalışmayı tercih ettim. Büyük veriler ile çalışmanın ne kadar kolay ve ne kadar keyifli olduğunu  görmeniz için özellikle büyük veri setleri kullandım.

Bir kere hazırladığımız dashboard larımız webde, windowsta ve mobilde aynı şekilde çalışmaktadır.

Örneğin aşağıdaki dashboard lar Logo Tiger datası üzerinden çekilen, Türkiye’nin 81 ilinde şubesi olan bir marketin satış rakamlarının analizini içeriyor. Her bir dashboardun ortalama oluşturulma süresi 10 dk civarında.

Veritabanı Açıklama:SQL Server üzerinde Logo Tiger Market Datası, Gerçek veriler ile örnekleme yapılarak Logo Objects kullanılarak oluşturuldu. Logo Objects dll saniyede 1 fatura kesebildiği için aralıksız çalışarak yaklaşık 10 günde oluşturuldu.

Rakamlar:

  • 3 GB MSSQL Verisi
  • 81 Şube
  • 600.000+Fatura
  • 2.500.000+Satış hareketi
  • 50.000+Müşteri
  • 20.000+Ürün (Çokonat, ariel, pınar gibi bildiğimiz ürünler. Marka ve özelliklerine göre kategorize edilmiş.)
  • 500+ Kasiyer
  • 12 aylık data

Bir sonraki örneğimiz ise dünya haritası üzerinde dünya ülkelerinin nüfus, zenginlik, tarım gibi özelliklerini değerlendirilmesi.

Yeni  eklediğim bir başka datasetimiz Avrupa Futbol Liglerinin maç istatistikleri. Bu verisetini csv olarak Kaggle com üzerinden indirdim. SQL veritanbanına attım ve data warehouse oluşturdum. CSV,XML ve Json dataları SQL formatında anlaşılır hale getirdim.

Futbolcu resimlerini temin etmek adınabir program yazdım ve Google görselleri üzerinde fubolcuları arayıp resimlerini kaydettim. (10.000+ futbolcu resmimiz de elimizde mevcut.)

Bu dataset üzerinde futbolcu istatistik analizi gerçekleştirdik. Örneğin aşağıdaki dashboard u 25 dk da sıfırdan yapabilirsiniz.

Veritabanı Açıklama:Avrupa Futbol Ligleri 8 yıllık istatistik bilgileri

Rakamlar:

  • 2 GB MSSQL Verisi
  • 10.000+ Futbolcu
  • 10.000+ Fotograf
  • 8 yıllık data
  • 11 Lig
  • 11 Ülke
  • 25.000+ Maç
  • 180.000+ Futbolcu özelliği
  • 500.000+ Futbolcu istatistik
  • 40.000 Gol istatistiği
  • 220.000 Faul istatistiği
  • 80.000+ Korner istatistiği
  • 60.000+ Sarı-Kırmızı Kart istatistiği
  • 180.000+ Şut istatistiği

Üzerinde halen çalıştığım veritabanı ise “Dünya Global Terörizm” Veritabanı

Amerikan Savunma Bakanlığı tarafından hazırlanan 45 yıllık terör saldırıları istatistikleri. Bu data Kaggle com üzerinden csv olarak indirildi ve SQL formatına çevrildi. Tüm içerik İngilizce olduğu için sistem Google translate api kullanılarak Türkçe’ye çevrildi.

Bing haritalar kullanılarak gerçek bir haritada konumlandırma içeren dashboardlar yapıldı. Yine bu dashboardların oluşturulması da dakikalar içerisinde gerçekleştirilmektedir.

Veritabanı Açıklama:45 yıllık global terör saldırıları veritabanı

Rakamlar:

  • 5 GB MSSQL Verisi
  • 170.000+Terör olayı
  • Tarih, saldırı türü, saldıran örgüt, hedef,saldırı sebebi, saldırı sonucu, kullanılan silah ve özetler gibi bir çok bilgiyi barındıran sözel içerik.
  • 58.000.000+ kelime
  • Tüm bu sözel içerik ve 58.000.000+ kelime İngilizceden Türkçe’ye cümle cümle çevirildi.
  • Çeviri için Google translate apisi kullanıldı.
  • Çeviri işlemi 5 gün sürdü.

    Benim hazırlarken çok emek verdiğim aynı zamanda bir o kadar da keyif aldığım bu kursu inşallah sizlerin de beğenmesi ve fayda sağlaması dileğiyle.
    Sevgiler.

    Ömer.

SQL Server için Performans Parametreleri 1 |Performans Monitor Uygulaması Giriş

Merhaba,

Bu yazımızda SQL Server’ın sağlıklı çalışıp çalışmadığını görmek için baktığımız çeşitli performans parametreleri hakkında Türkçe olarak konuşacağız.

Öncelikle bilmeyenler için performans parametre ve sayarçlarına performance monitör (perfmon.exe) uygulaması ile bakıyoruz.

Yanlış anlaşılmasın bu uygulama windows’un içinde gelen bir uygulama yani sonradan kurulmuyor.

Başlat>Çalıştır’a perfmon yazarsak gelecektir.

image001.png

image002.png

Çalıştırdığımızda karşımıza böyle bir ekran çıkar.image003.png

Buradan istediğimiz sayaç değerini istediğimiz formatta görebiliyoruz. Örneğin yukarıda bu değerleri grafiksel olarak görürken aşağıdaki resimde Rapor işaretleyerek sayısal olarak görebiliyoruz.image004.jpg

image005.png

Şimdi bazı sayaçları ekleyerek değerler bakalım.

image006.png

Gördüğünüz gibi yüzlerce sayacımız var.

image007.png

Şimdi bunlardan en çok kullanılanlardan bir kaçını ekleyelim.

Örneğin aşağıdaki resimde diskte ortalama bekleyen işlem kuyruğu sayısı sayacını ekledim. (Ortalama disk sırası uzunluğu ya da Average Disk Que Length) değeri.image009.png

Burada tüm diskler için seçebildiğim gibi sadece bir veya birkaç diski de seçebiliyorum.

image010.jpg

Eklediğim sayaçları da bu şekilde izleyebiliyorum.

image012.pngBuradaki resimde ise başka sayaçlar da eklenmiş durumda.

image013.jpg

Bir sonraki yazıda bu parametreler nelerdir? Ne işe yarar ve olması gereken değerler nelerdir onları konuşuyor olacağız.

SQL Server ile Web Servise bağlanıp Json formatında hava durumunu çekme

SQL Server ile Web Servise bağlanıp Json formatında hava durumunu çekme

Son dönemde bildiğiniz üzere farklı platformlar arasında en çok kullanılan ortak dil JSON oldu. Öyle ki 2000’li yılların başında bu noktada bir devrim gibi çıkan web servislerinin formatı olan XML’in yerini sahip olduğu avantajlar ile almış durumda.

JSON bu kadar popüler iken data ile uğraşan insanlar olarak bizim de MSSQL tarafında bu konuya kayıtsız kalmamız doğru olmaz diye düşünüyorum ve JSON formatını SQL tablosuna  dönüştüren bir fonksiyonu sizinle burada paylaşıyorum. Her ne kadar 2017 versiyonunda MSSQL Json’a destek verse de önceki versiyonlarda kullanacağınız bir fonksiyonu paylaşıyorum.

Bu kodu bizimle paylaşan Scott Puleo’ya teşekkür ederiz.

Aşağıdaki linkte bulabilirsiniz.

Şimdi gelelim örneğimize internette hava durumunu api olarak json formatında bize veren bir site var.

https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=b6907d289e10d714a6e88b30761fae22

Örneğin burası bize Londra ile alakalı JSON bilgi döndürüyor.

Buradaki Json bilgisini bizim fonksiyonumuzda çağırdığımızda ise aşağıdaki gibi sonuç görürüz.

Şimdi bu işlemi web sayfasından doğrudan çekecek şekilde kodumuzu yazalım.

Bunun için Ole Automation procedure leri kullanacağız. Bu procedure leri kullanmak için

exec sp_configure ‘show advanced options’,1

reconfigure with override

exec sp_configure ‘Ole Automation Procedures’,1

reconfigure with override

Diyerek aktif hale getiriyoruz.

Sonra kodumuzu çalıştırdığımızda sonuç aşağıdaki gibi geliyor. Burada bu site bir örnek ve normalde paralı olarak bu hizmeti veriyor. Başka uygulamalara da bakılabilir. Ben ilk karşıma çıkan sayfayı denedim açıkçası.

Kodları aşağıda paylaşıyorum.

Önce JSON dbo.parseJSON fonksiyonunu oluşturmanız gerekiyor. Onun scripti uzun olduğu için en aşağıda. Sonra hemen alttaki sorguyu çalıştırmanız yeterli.

Görüşmek üzere.

Declare @RESULT as varchar(MAX)
Declare @JSON as varchar(MAX)
Declare @Object as int;
Declare @ResponseText as varchar(8000);
Declare @HResult int
Declare @Source varchar(255), @Desc varchar(255)
Declare @Body as varchar(8000) =”

—————————Web Servise Bağlanıp hava durumunu çeken kod———————
Declare @URL as varchar(MAX)=’https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=b6907d289e10d714a6e88b30761fae22′
Exec SP_OACreate ‘MSXML2.XMLHTTP’, @Object OUT;
Exec SP_OAMethod @Object, ‘Open’, NULL, ‘GET’,
@URL, ‘false’
Exec SP_OAMethod @Object, ‘setRequestHeader’, NULL, ‘Content-Type’, ‘application-json’
Declare @Len int
Set @Len = len(@Body)
Exec SP_OAMethod @Object, ‘send’, NULL
Exec SP_OAMethod @Object, ‘ResponseText’, @ResponseText OUTPUT
Set @JSON = @ResponseText

select * from dbo.parseJSON(@JSON)

——————————–json formatını parse eden fonksiyon————————
CREATE FUNCTION dbo.parseJSON( @JSON NVARCHAR(MAX))
RETURNS @hierarchy TABLE
(
element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
sequenceNo [int] NULL, /* the place in the sequence for the element */
parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
NAME NVARCHAR(2000),/* the name of the object */
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
)
AS
BEGIN
DECLARE
@FirstObject INT, –the index of the first open bracket found in the JSON string
@OpenDelimiter INT,–the index of the next open bracket found in the JSON string
@NextOpenDelimiter INT,–the index of subsequent open bracket found in the JSON string
@NextCloseDelimiter INT,–the index of subsequent close bracket found in the JSON string
@Type NVARCHAR(10),–whether it denotes an object or an array
@NextCloseDelimiterChar CHAR(1),–either a ‘}’ or a ‘]’
@Contents NVARCHAR(MAX), –the unparsed contents of the bracketed expression
@Start INT, –index of the start of the token that you are parsing
@end INT,–index of the end of the token that you are parsing
@param INT,–the parameter at the end of the next Object/Array token
@EndOfName INT,–the index of the start of the parameter at end of Object/Array token
@token NVARCHAR(200),–either a string or object
@value NVARCHAR(MAX), — the value as a string
@SequenceNo int, — the sequence number within a list
@name NVARCHAR(200), –the name as a string
@parent_ID INT,–the next parent ID to allocate
@lenJSON INT,–the current length of the JSON String
@characters NCHAR(36),–used to convert hex to decimal
@result BIGINT,–the value of the hex symbol being parsed
@index SMALLINT,–used for parsing the hex value
@Escape INT –the index of the next escape character

DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are ‘escaped’ in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */
(
String_ID INT IDENTITY(1, 1),
StringValue NVARCHAR(MAX)
)
SELECT–initialise the characters to convert hex to ascii
@characters=’0123456789abcdefghijklmnopqrstuvwxyz’,
@SequenceNo=0, –set the sequence no. to something sensible.
/* firstly we process all strings. This is done because [{} and ] aren’t escaped in strings, which complicates an iterative parse. */
@parent_ID=0;
WHILE 1=1 –forever until there is nothing more to do
BEGIN
SELECT
@start=PATINDEX(‘%[^a-zA-Z][“]%’, @json collate SQL_Latin1_General_CP850_Bin);–next delimited string
IF @start=0 BREAK –no more so drop through the WHILE loop
IF SUBSTRING(@json, @start+1, 1)='”‘
BEGIN –Delimited Name
SET @start=@Start+1;
SET @end=PATINDEX(‘%[^\][“]%’, RIGHT(@json, LEN(@json+’|’)-@start) collate SQL_Latin1_General_CP850_Bin);
END
IF @end=0 –no end delimiter to last string
BREAK –no more
SELECT @token=SUBSTRING(@json, @start+1, @end-1)
–now put in the escaped control characters
SELECT @token=REPLACE(@token, FROMString, TOString)
FROM
(SELECT
‘\”‘ AS FromString, ‘”‘ AS ToString
UNION ALL SELECT ‘\\’, ‘\’
UNION ALL SELECT ‘\/’, ‘/’
UNION ALL SELECT ‘\b’, CHAR(08)
UNION ALL SELECT ‘\f’, CHAR(12)
UNION ALL SELECT ‘\n’, CHAR(10)
UNION ALL SELECT ‘\r’, CHAR(13)
UNION ALL SELECT ‘\t’, CHAR(09)
) substitutions
SELECT @result=0, @escape=1
–Begin to take out any hex escape codes
WHILE @escape>0
BEGIN
SELECT @index=0,
–find the next hex escape sequence
@escape=PATINDEX(‘%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%’, @token collate SQL_Latin1_General_CP850_Bin)
IF @escape>0 –if there is one
BEGIN
WHILE @index<4 –there are always four digits to a \x sequence
BEGIN
SELECT –determine its value
@result=@result+POWER(16, @index)
*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
@characters)-1), @index=@index+1 ;

END
— and replace the hex sequence by its unicode value
SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))
END
END
–now store the string away
INSERT INTO @Strings (StringValue) SELECT @token
— and replace the string with a token
SELECT @JSON=STUFF(@json, @start, @end+1,
‘@string’+CONVERT(NVARCHAR(5), @@identity))
END
— all strings are now removed. Now we find the first leaf.
WHILE 1=1 –forever until there is nothing more to do
BEGIN

SELECT @parent_ID=@parent_ID+1
–find the first object or list by looking for the open bracket
SELECT @FirstObject=PATINDEX(‘%[{[[]%’, @json collate SQL_Latin1_General_CP850_Bin)–object or array
IF @FirstObject = 0 BREAK
IF (SUBSTRING(@json, @FirstObject, 1)='{‘)
SELECT @NextCloseDelimiterChar=’}’, @type=’object’
ELSE
SELECT @NextCloseDelimiterChar=’]’, @type=’array’
SELECT @OpenDelimiter=@firstObject

WHILE 1=1 –find the innermost object or list…
BEGIN
SELECT
@lenJSON=LEN(@JSON+’|’)-1
–find the matching close-delimiter proceeding after the open-delimiter
SELECT
@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,
@OpenDelimiter+1)
–is there an intervening open-delimiter of either type
SELECT @NextOpenDelimiter=PATINDEX(‘%[{[[]%’,
RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)–object
IF @NextOpenDelimiter=0
BREAK
SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter
IF @NextCloseDelimiter<@NextOpenDelimiter
BREAK
IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{‘
SELECT @NextCloseDelimiterChar=’}’, @type=’object’
ELSE
SELECT @NextCloseDelimiterChar=’]’, @type=’array’
SELECT @OpenDelimiter=@NextOpenDelimiter
END
—and parse out the list or name/value pairs
SELECT
@contents=SUBSTRING(@json, @OpenDelimiter+1,
@NextCloseDelimiter-@OpenDelimiter-1)
SELECT
@JSON=STUFF(@json, @OpenDelimiter,
@NextCloseDelimiter-@OpenDelimiter+1,
‘@’+@type+CONVERT(NVARCHAR(5), @parent_ID))
WHILE (PATINDEX(‘%[A-Za-z0-9@+.e]%’, @contents collate SQL_Latin1_General_CP850_Bin))<>0
BEGIN
IF @Type=’Object’ –it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT
@SequenceNo=0,@end=CHARINDEX(‘:’, ‘ ‘+@contents)–if there is anything, it will be a string-based name.
SELECT @start=PATINDEX(‘%[^A-Za-z@][@]%’, ‘ ‘+@contents collate SQL_Latin1_General_CP850_Bin)–AAAAAAAA
SELECT @token=SUBSTRING(‘ ‘+@contents, @start+1, @End-@Start-1),
@endofname=PATINDEX(‘%[0-9]%’, @token collate SQL_Latin1_General_CP850_Bin),
@param=RIGHT(@token, LEN(@token)-@endofname+1)
SELECT
@token=LEFT(@token, @endofname-1),
@Contents=RIGHT(‘ ‘+@contents, LEN(‘ ‘+@contents+’|’)-@end-1)
SELECT @name=stringvalue FROM @strings
WHERE string_ID=@param –fetch the name
END
ELSE
SELECT @Name=null,@SequenceNo=@SequenceNo+1
SELECT
@end=CHARINDEX(‘,’, @contents)– a string-token, object-token, list-token, number,boolean, or null
IF @end=0
SELECT @end=PATINDEX(‘%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%’, @Contents+’ ‘ collate SQL_Latin1_General_CP850_Bin)
+1
SELECT
@start=PATINDEX(‘%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%’, ‘ ‘+@contents collate SQL_Latin1_General_CP850_Bin)
–select @start,@end, LEN(@contents+’|’), @contents
SELECT
@Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
@Contents=RIGHT(@contents+’ ‘, LEN(@contents+’|’)-@end)
IF SUBSTRING(@value, 1, 7)=’@object’
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5), ‘object’
ELSE
IF SUBSTRING(@value, 1, 6)=’@array’
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),
SUBSTRING(@value, 7, 5), ‘array’
ELSE
IF SUBSTRING(@value, 1, 7)=’@string’
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, stringvalue, ‘string’
FROM @strings
WHERE string_ID=SUBSTRING(@value, 8, 5)
ELSE
IF @value IN (‘true’, ‘false’)
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘boolean’
ELSE
IF @value=’null’
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘null’
ELSE
IF PATINDEX(‘%[^0-9]%’, @value collate SQL_Latin1_General_CP850_Bin)>0
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘real’
ELSE
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘int’
if @Contents=’ ‘ Select @SequenceNo=0
END
END
INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT ‘-‘,1, NULL, ”, @parent_ID-1, @type

RETURN
END
GO

SQL Server’da Ethernet Jumbo Paket Sorunu

Uzun zaman önce yaşadığım ve çözümünü çok zor bulduğum bir sorunu burada paylaşmak istiyorum. SQL deki yavaşlığın ethernetten kaynaklandığını tespit ettiğimiz çok ilginç bir deneyim.

2011 yılında başıma gelen bir olay.

Bir sistem upgrade’i yaptık. Server ımız değişti, Sistemde database olarak SQL 2005’ten SQL 2008’e, İşletim sistemi olarak Windows Server 2003’ten Windows Server 2008’e geçtik ve cluster yapısı kurduk. ERP Sistemimizde de versiyon geçişi yaptık ve yeni versiyonun çalışması için client bilgisayarlarda mdac versiyon upgrade’ine ihtiyaç duyduk.
Sonuç olarak çok ilginç bir sorunla karşılaştık. Kullanıcı tarafındaki çok basit bir işlem kimi bilgisayarda 1 sn sürerken kimi bilgisayarda 20 sn sürüyordu.
Bu durumda sorun server kaynaklı, İşletim sistemi kaynaklı, SQL 2008 kaynaklı, Erp programı kaynaklı, ya da mdac kaynaklı olabilirdi. Çünkü bunların hepsi de değişmişti. Epey bir inceleme yaptık sorun üzerinde.
Öncelikle şunu söyleyim bu ayarla alakalı sql server üzerinde bir makale bulamadım.
Ancak başka uygulamalarda benzer sıkıntılar yaşanmış onun üzerine bu konu üzerine gittik.
Burada problem şu;
Yeni ethernetler Jumbo frame denilen yapıyı destekliyor ve normalde 1500 byte lık olan network paketleri 9000 byte olarak tek seferde gönderiyor. Paketleri parçalama işini client ın etherneti yapıyor. Performans amaçlı yapılan bir iyileştirme.
Özellikle benzer işlem tekrarlarında bu durumu sistem otomatik olarak yapıyor yani kendince optimize etmeye çalışıyor. Client’ta özellikle döngüye takıp aynı sonucu döndüren tek satırlık ya da çoğunlukla sıfır satırlık select cümlelerinin kullanıldığı yerlerde bu özellik devreye giriyor.
Eğer karşıdaki ethernet jumbo paketi desteklemiyor ise paket tekrardan servera gönderiliyor bu kez server bu paketi tekrardan parçalayıp client a gönderiyor. Bu da her paket için yapıldığında yaklaşık 10 kat bazen daha fazla gecikmeye sebep oluyor.

Çözüm iki türlü.
Ya server’dan “Large Recive Offload Data” özelliğini disable etmek ya da client da jumbo paket size değerini arttırmak.
Ancak clientta işlem yapmanın iki dezavantajı var bunlardan biri ethernet ya da switchler desteklemiyor olabilir ikincisi de bu özellik enable yapıldığında networkte büyük paketler dolaşmaya başladığından networkü tıkayabilir.
Bu konuda en doğrusunun server üzerinde bu ayarı disable etmek olduğuna karar verdik.
Biz bu ayarı serverda disable ederek sorunu çözdük. Zaten eski serverda ethernet desteklemiyormuş ondan sorun olmamış.
Bu bahsettiğim sorundan kaynaklı sıkıntı olduğunu düşündüğün makinada sorun olup olmadığını anlamak için performance monitorden send packet/sec değerlerine bakılabilir.
Hızlı makina ile yavaş makina arasındaki fark en az 10 kat oluyor. Aşağıdaki resimde ise bu ayarın nasıl yapıldığını görüyorsunuz.

Sağlıcakla…

jumbo.jpg

Logon Trigger ile Sql Server’a İzin Verilmeyen Bilgisayarların Girmesini Engelleme

Bu yazımızda Logon trigger lar ile SQL Server’a bizim iznimiz dışında herhangi bir bilgisayardan kullanıcı adı şifre bilinse bile (SA kullanıcısı dahil) girilmesini engelleme uygulaması yapacağız.

Burada bir izin verilen bilgisayar listemiz var ve bunlar bir tabloda tutuluyor. Biz bu listenin dışında herhangi bi client dan login olma işlemi geldiğinde şunları yapabiliriz.

1.Kayıt altına alabiliriz.

2.Kayıt altına alıp anında sistem yöneticisine mail attırabiliriz.

3.Login olmasını engelleyebiliriz.

İşte bütün bu işlemleri yapmak için Logon Trigger yazmamız gerekiyor.

Trigger ları normalde tabloların altında insert,update,delete işlemleri için kullanırız. Ancak pek az bilinse de SQL Server 2008 sürümünden bu yana hem ddl trigger lar hem de logon trigger lar mevcut. Bu trigger lar sayesinde bir çok brut force atağın önüne kolaylıkla geçilebilir.

Böylece Kullanıcı adı, şifre, port önlemlerinin üstüne bir de client adı kontrolü ile ekstra bir koruma sağlamış oluyoruz.

Şimdi bu işlemi adım adım nasıl yaptığımızı anlatalım.

1.Öncelikle sisteme Audit isimli bir database oluşturalım. Bu database bizim loğları tutacağımız ve izin verilen bilgisayarları tanımlayacağımız yer.

 

2.Bu database in içine AuditLog isimli bir tablo oluşturalım.

CREATE TABLE [dbo].[AUDITLOG](

[SESIONID] [INT] NULL,

[LOGONTIME] [DATETIME] NOT NULL,

[NET_TRANSPORT] [VARCHAR](250) NOT NULL,

[PROTOCOL_TYPE] [VARCHAR](250) NULL,

[AUTH_TYPE] [VARCHAR](250) NOT NULL,

[NET_PACKETSIZE] [INT] NULL,

[CLIENT_NETADDRESS] [VARCHAR](250) NULL,

[PROGRAM_NAME] [VARCHAR](250) NOT NULL,

[HOSTNAME] [VARCHAR](250) NOT NULL,

[NT_USERNAME] [VARCHAR](250) NOT NULL,

[NET_LIBRARY] [VARCHAR](250) NOT NULL,

[NT_DOMAIN] [VARCHAR](250) NOT NULL

) ON [PRIMARY]

 

Burada gördüğümüz üzere

SESIONID: SQl Server a bağlanan kullanıcının id si.

LOGONTIME: Sisteme login olmaya çalıştığı zaman

NET_TRANSPORT: TCP , Shared Memory, Named Pipes gibi protokollerden hangisini kullandığı

PROTOCOL_TYPE: SQL Server ile konuşma dili (TSQL)

AUTH_TYPE: Authentication türü (NTLM,SQL)

NET_PACKETSIZE: Network paketi boyutu (4096,8000….)

CLIENT_NETADDRESS: Bağlanan kullanıcının Ethernet ya da IP Adresi

PROGRAM_NAME: SQL e bağlantı kurulmaya çalışılan uygulama (Management studio…)

HOSTNAME: Bağlanan kullanıcının bilgisayarının adı

NT_USERNAME: Bağlanan kullanıcının Windows kullanıcısının adı

NET_LIBRARY: Network kütüphanesi (TCP/IP, LPC…)

NT_DOMAIN:Kullanıcı domaini

3.Hangi bilgisayarlara izin vereceğimizi tutmak adına ALLOWEDHOST isimli bir tablo oluşturalım ve altına kendi bilgisayarımızı ekleyelim ki bağlanmamıza izin versin.

CREATE TABLE ALLOWEDHOST (HOSTNAME VARCHAR(200))

INSERT INTO ALLOWEDHOST (HOSTNAME) VALUES (‘OMERLENOVO’)

 

4.Şimdi trigger ımızı yazıyoruz. Trigger aşağıdaki gibi. Kodların ne işe yaradığını yorum satırlarında yazdım.

CREATE TRIGGER [connection_AUDIT] –TRIIGER ADI

ON ALL SERVER — BÜTÜN DATABASE LER İÇİN GEÇERLİ

FOR LOGON –LOGON İŞLEMİ SIRASINDA ÇALIŞACAK

AS

BEGIN

DECLARE @HOSTNAME AS VARCHAR(200) –KULLANICININ HOST BİLGİSİNİ ALMAK İÇİN DEĞİŞKEN TANIMLIYORUZ

SELECT @HOSTNAME=HOST_NAME() –HOST_NAME() FONKSİYONU CLIENT MAKİNENİN ADINI VERİR

 

IF NOT EXISTS (SELECT * FROM AUDIT.DBO.ALLOWEDHOST WHERE HOSTNAME=@HOSTNAME)

–EĞER KULLANICI BİLGİSAYARI BİZİM İZİN VERDİĞİMİZ LİSTEDE YOK İSE

–master.sys.dm_exec_connections VE master.dbo.sysprocesses SİSTEM VIEWLARINI KULLANARAK İHTİYACIMIZ OLAN BİLGİLERİ

–ALIYORUZ VE AUDITLOG TABLOSUNA INSERT EDIYORUZ.

BEGIN

INSERT INTO AUDIT.DBO.AUDITLOG (SESIONID, LOGONTIME, NET_TRANSPORT, PROTOCOL_TYPE, AUTH_TYPE, NET_PACKETSIZE, CLIENT_NETADDRESS,

PROGRAM_NAME, HOSTNAME, NT_USERNAME, NET_LIBRARY, NT_DOMAIN)

select

cnn.session_id SESIONID,cnn.connect_time LOGONTIME,cnn.net_transport NET_TRANSPORT,

cnn.protocol_type PROTOCOL_TYPE,

cnn.auth_scheme AUTH_TYPE,

cnn.net_packet_size NET_PACKETSIZE,

cnn.client_net_address CLIENT_NETADDRESS,

prc.program_name PROGRAM_NAME,

hostname HOSTNAME,

nt_username NT_USERNAME,

net_library NET_LIBRARY,nt_domain NT_DOMAIN

 

from master.sys.dm_exec_connections cnn

inner join master.dbo.sysprocesses prc

on prc.spid = cnn.session_id

WHERE PRC.spid=@@SPID

 

END

END

 

5.Management studioda Server Objects kısmında yazdığımız trigger ı görebiliriz.


6.Şimdi sisteme Login olalım.

Sistem girmeye izin verdi. Çünkü girdiğim makinenin adı OMERLENOVO ve bu isim ALLOWEDHOST tablosunda mevcut.

 

7.Kendi bilgisayarımızın adını izin verilen bilgisayarlar listesinden çıkaralım. Bunun için OMERLENOVO yazan yeri OMERLENOVO1 diye değiştiriyorum.

8.Şimdi yeni bir ekrandan bağlanmaya çalışıyorum.

Gördüğümüz gibi sisteme yine login olduk.

 

9.Log tablomuza bakalım.

OMERLENOVO makinesinden login olma işlemi kayıt altına alındı. Çünkü bu makine ALLOWEDHOST tablosunda yok ve yabancı bir makine. Burada otomatik olarak çalıştıracağımız bir job ile sistem yöneticisine bu durumu mail de attırabiliriz.

10.Eğer biz belirli bilgisayarlar dışında hiçbir bilgisayarın sisteme girememesini istiyorsak bu kez ROLLBACK yaparak bunu sağlayabiliriz.

ALTER TRIGGER [connection_AUDIT] –TRIIGER ADI

ON ALL SERVER — BÜTÜN DATABASE LER İÇİN GEÇERLİ

FOR LOGON –LOGON İŞLEMİ SIRASINDA ÇALIŞACAK

AS

BEGIN

DECLARE @HOSTNAME AS VARCHAR(200) –KULLANICININ HOST BİLGİSİNİ ALMAK İÇİN DEĞİŞKEN TANIMLIYORUZ

SELECT @HOSTNAME=HOST_NAME() –HOST_NAME() FONKSİYONU CLIENT MAKİNENİN ADINI VERİR

 

IF NOT EXISTS (SELECT * FROM AUDIT.DBO.ALLOWEDHOST WHERE HOSTNAME=@HOSTNAME)

–EĞER KULLANICI BİLGİSAYARI BİZİM İZİN VERDİĞİMİZ LİSTEDE YOK İSE DOĞRUDAN ROLLBACK YAPIYORUZ

 

ROLLBACK

 

END

 

11.Şimdi sisteme login olmaya çalışalım. (Bu kısım tehlikeli yeni bir management studio açalım çünkü bir daha hiç giremeyebiliriz.

Görüldüğü üzere sisteme girmeye izin vermedi.

12.Şimdi ALLOWEDHOST tablosunu OMERLENOVO1 yazan yeri OMERLENOVO olarak tekrar düzeltelim.

Tekrar girmeyi denediğimizde görüldüğü gibi giriş yapabiliyoruz.

 

Sonuç:

Eğer SQL Server sistemine bir saldırı olma ihtimali üzerine önlem alıyorsak,

Diyelim ki portları kontrol ettik,

Kullanıcı adı ve şifreleri kompleks yaptık

Ancak kullanıcı adı ve şifre birinin eline geçebilir ve bizim networkümüze başka bir bilgisayar da dahil olabilir.

Ya da yabancı bir bilgisayar brut force yaparak şifre denemesinde bulunabilir.

Bunun önüne geçmek adına LOGON triggerlar hem izleme hem de engelleme noktasında oldukça faydalı.

Son olarak bu denemeleri dikkatli yapmak gerekir. Ziraz kendi kendimizi engelleyebilirizJ. Siz siz olun bu işlemleri yapmadan önce master.mdf ve mastlog.ldf dosyalarını yedekleyin. Olur ki kendi kendinizi engellersiniz yapacağınız tek şey bu master database ine ait dosyaların eskisine dönmek.

SQL Server Brute Force Ataklarını Tespit Etme Ve Engelleme

Merhaba, SQL Server logging ve auditing konusunda yazılarımıza bir yenisi ile devam ediyoruz. Bu makalemizde SQL Server (aslında kullanıcı adı ve şifre ile girilen her platformda geçerli olan) brute force ataklarını tespit etme, engelleme ve saldırganı iş üstünde yakalama konularına değineceğiz.

Kısaca brute force nedir? Biraz bahsedelim.

Brute force, rastgele anlamsız ya da anlamlı şekilde şifre deneyerek sisteme girmeye  çalışmak demektir. Bu ataklarda ya tüm yazı karakterleri sırayla anlamsız da olsa denenir, ya da internette bir çok yerde bulabileceğiniz şifre sözlüğü listelerindeki en çok kullanılan şifreler ile saldırılır.

Birinci yöntem uzun sürmesine rağmen sonuca ulaşma olsılığı daha yüksektir. İkinci yöntemde ise durum biraz şansa kalmış. Tabi bu sözlük listelerinde bile 5-10 milyon şifre bulunmakta.

Belki de yazının sonunda yazmamız gereken konuyu olur da sonuna kadar sabredemeyenler olur diye şimdi söylemek istiyorum. Brute force atakta ihtiyacımız olan,

  • IP
  • Port
  • Kullanıcı adı
  • Şifre listesi

Burada dışarıdan saldırılar için portu dışarıya açmamak bir çözüm. Ayrıca firewalllar bu saldırıları farkedip engelleyebiliyor. Ama içeriden olan saldırılar çok daha riskli. Bu anlamda IP bellidir, portu da kapatamazsınız. Bu durumda kullanıcı adı default SA kullanıcısını disable etmek en mantıklı çözüm. Zira saldırgan hem şifreyi hem kullanıcı adını bulmak zorunda kalacak. Tabi bu arada siz onu bulamazsanız.

Brute force ataklarını tespit etmek ve engellemek için çeşitli 3. Parti sistemler ve yazılımlar mevcut. Ancak bunlar oldukça maliyetli sistemler ve iyi haber, SQL Server ı iyi biliyorsanız bu korumayı kendiniz yapabilirsiniz.

Bu makalede şunu gerçekleştiriyor olacağız.

  • 2 Milyon kayıt içeren bir şifre sözlüğümüz var.
  • Bu şifreler ile sisteme saldıran bir yazılımımız var.
  • 3 dk da bir SQL Server’ın sistem logunu okuyacağız ve sisteme login failed ile düşen 3 dk da belli bir sayıdan fazla (burada ben 100 olarak belirttim.) event var mı ona bakacağız.
  • Eğer varsa bu bilgisayarı tespit edip kara liste olarak tanımladığımız tablomuza kaydedeceğiz.
  • Sisteme koyacağımız bir logon trigger ile bu makine üzerinden gelen login isteğini reddedeceğiz.
  • Saldırgan şifreyi kırsa bile kıramadığını sanıp saldırmaya devam edecek.
  • Biz ise onu iş üstünde yakalayacağız.

Öncelikle brute force atak yapacak bir yazılımımız var. Aşağıdaki kodları ile birlikte aşağıdaki linkten indirebilirsiniz.

https://drive.google.com/open?id=0B1ie-PwA7YrMQnA3RWVOMW15dEU

Programı çalıştırdığımızda aşağıdaki ekranı görüyor olacağız.

Burada,

Sunucu:Bağlanacağımız SQL Server adı

Kullanıcı :SA

Database:Master

Olarak ayarlayabiliriz. Benim burada denediğim makinede password bilgisi “Password1” olarak belirlendi ve “Password1” kelimesini uniquePass_preview.txt şifre sözlüğü dosyasının içinde başlarda bir yere koyarak sistemin hemen şifreyi bulmasını sağladım.

Örneğin bendeki dosyada 19. Sırada.

Programı çalıştırdığımızda aldığımız görüntü ise şu şekilde. Görüldüğü gibi 19. Sıradaki bir şifreyi 5 sn de buldu. İlginç bir şekilde networkteki başka bir makineden local makineye göre daha hızlı bulduğunu farkettim.

Şimdi burada bizim amacımız önce brute force yapıldığını, sonra da brute force yapanı tespit etmek ve kara listeye düşürmek.

Burada tüm saldırıların içeriden olduğunu tekrar hatırlatmak isterim.

Sisteme brute force yapıldığında sistem SQL Server log dosyasına ve Windows log dosyasına

“Login failed for user ‘SA’. Reason: Password did not match that for the login provided. [CLIENT: 192.168.1.24]”

Şeklinde bir kayıt atmaktadır. Bunu aşağıdaki şekilde management studio üzerinden görebiliriz.

 

Şimdi biz burada log dosyasına kendimiz bakabiliyoruz ancak yapmak istediğimiz, 3 dakikada bir çalışacak bir job ile 3 dakika içerisinde birden çok kez (ben 100 olarak belirledim) Login failed hatası alıyorsak atak yapıldığını anlayacak ve yöneticiyi uyaracak bir sistem geliştirmek.

Öncelikle log dosyasını sorgu ile okumamız gerekiyor.

EXEC xp_readerrorlog 0;

Komutu log dosyasını okumamızı sağlar. Komutun ekran çıktısı aşağıdaki gibidir.

Görüldüğü üzere burada bir çok log hareketi var. Bizim için önemli olan ise “Login failed…” şeklindeki mesajlar.

Bu mesajları filtrelemek için bir temp tablo oluşturuyoruz ve stored procedure den dönen değerleri bu tabloya dolduruyoruz. Aşağıdaki script bu işi gerçekleştiriyor.

CREATE TABLE #SQLErrorLog

(

LogDate DATETIME ,

ProcessInfo VARCHAR(20) ,

Text VARCHAR(500)

);

INSERT  INTO #SQLErrorLog

EXEC xp_readerrorlog 0;

Daha sonra bu tablodan Text alanı “Login failed…” şeklinde olanları çekiyoruz.

SELECT * FROM #SQLErrorLog WHERE Text LIKE ‘%Login failed%’

Yine son 3 dakika öncekileri çekiyoruz.

SELECT * FROM #SQLErrorLog WHERE Text LIKE ‘%Login failed%’ AND logdate>=DATEADD(MINUTE,-3,GETDATE())

Bu sorgudan 18 satır geliyor. Çünkü biz 19. Sırada doğru şifreyi bulmuş ve login olmuştuk.

Şimdi UniqePass_preview.txt dosyasında bulunan Password1 textini daha aşağıya taşıyalım. Örneğin ben 180. Satıra taşıdım.

Şimdi programı tekrar çalıştırıyoruz.

Görüldüğü gibi bu kez 179 denemede ve 1dk 16 sn de buldu şifreyi. Şimdi son 3 dakikalık loglara tekrar bakalım.

Görüldüğü gibi bu kez 179 den fazla kayıt var ve bu bizim belirlediğimiz eşik değeri olan 100 den fazla o zaman bir saldırı var demektir.

Şimdi bunun için bir sorgu oluşturacağız. Bu sorgu sistemde brute force atak algıladığında SQL DB mail ile admine mail gönderecek. Aşağıdaki sorgu bu işi yapıyor.

DECLARE @COUNT AS INT

DECLARE @TEXT AS VARCHAR(8000)

SELECT @TEXT=TEXT,@COUNT=COUNT (*) FROM #SQLErrorLog WHERE Text LIKE ‘%Login failed%’ ANDlogdate>=DATEADD(MINUTE,-3,GETDATE())

GROUP BY TEXT HAVING COUNT(*)>100

IF @COUNT>100

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name=”BIM”,

@recipients=’ocolakoglu@gmail.com’,

@subject = ‘Sisteminize 100 den fazla brute force şifre denemesi yapıldı’,

@body = ‘Sisteminize 100 den fazla brute force şifre denemesi yapıldı<br>’,–+ @text ,

@body_format = ‘HTML’ ;

END

DROP TABLE #SQLErrorLog

Şimdi bu sorguyu derli toplu olması adına stored procedure yapalım.

Create PROC BRUTE_FORCE_CONTROL AS

CREATE TABLE #SQLErrorLog

(

LogDate DATETIME ,

ProcessInfo VARCHAR(20) ,

Text VARCHAR(500)

);

INSERT  INTO #SQLErrorLog

EXEC xp_readerrorlog 0;

DECLARE @COUNT AS INT

DECLARE @TEXT AS VARCHAR(8000)

SELECT @TEXT=TEXT,@COUNT=COUNT (*) FROM #SQLErrorLog WHERE Text LIKE ‘%Login failed%’ ANDlogdate>=DATEADD(MINUTE,-3,GETDATE())

GROUP BY TEXT HAVING COUNT(*)>100

IF @COUNT>100

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name=”BIM”,

@recipients=’ocolakoglu@gmail.com’,

@subject = ‘Sisteminize 100 den fazla brute force şifre denemesi yapıldı’,

@body = ‘Sisteminize 100 den fazla brute force şifre denemesi yapıldı<br>’,–+ @text ,

@body_format = ‘HTML’ ;

END

DROP TABLE #SQLErrorLog

Bundan sonra ise 3 dk da bir çalışacak bir SQL Job oluşturacağız.

Sql Job’ımızı da oluşturduk. Manuel olarak çalıştıralım. Bundan sonra otomatik olarak çalışacaktır.

Şimdi brute force programımız ile saldıralım.

Görüldüğü gibi sistem otomatik mail gönderiyor.

Buraya kadar saldırganı tespit etme çalışması yaptık. Bundan sonrasında ise saldırganın içeriye girmesine engel olacağız.

Bunun için otomatik çalışan job ımız burada okuduğu ip yi bir blacklist tablosuna atacak.

Kullanacağımız tablo aşağıdaki gibi.

CREATE TABLE [dbo].BLACKLIST(

[ID] [INT] IDENTITY(1,1) NOT NULL,

[COMPUTERIP] [VARCHAR](250) NULL,

[DATE_] [DATETIME] NOT NULL,

[MINDATE] [DATETIME] NULL,

[MAXDATE] [DATETIME] NULL,

[ERRORTEXT] [VARCHAR](5000) NULL)

Sistemde 3 dk da bir çalışacak job için oluşturduğumuz stored procedure ü aşağıdaki gibi değiştiriyoruz.

ALTER PROC [dbo].[BRUTEFORCE_CONTROL]

AS

CREATE TABLE #SQLErrorLog

(

LogDate DATETIME ,

ProcessInfo VARCHAR(20) ,

Text VARCHAR(500)

);

INSERT  INTO #SQLErrorLog

EXEC xp_readerrorlog 0;

DECLARE @TEXT AS VARCHAR(1000);

DECLARE @COUNT AS INT;

DECLARE @MINDATE AS DATETIME;

DECLARE @MAXDATE AS DATETIME;

SELECT  @TEXT = Text ,

@COUNT = COUNT(*) ,

@MINDATE = MIN(LogDate) ,

@MAXDATE = MAX(LogDate)

FROM    #SQLErrorLog

WHERE   ( Text LIKE ‘%Login failed for user%’ )

AND LogDate >= DATEADD(MINUTE, -3, GETDATE())

GROUP BY Text

HAVING  COUNT(*) > 100;

SET @COUNT=ISNULL(@COUNT,0)

IF @COUNT>5

BEGIN

DECLARE @CLIENT AS VARCHAR(50)

DECLARE @POS1 AS INT

DECLARE @POS2 AS INT

SELECT @POS1=CHARINDEX(‘CLIENT:’,@TEXT)+8

SELECT @POS2=CHARINDEX(‘]’,@TEXT)

SELECT @CLIENT=SUBSTRING(@TEXT,@POS1,@POS2-@POS1)

DECLARE @MSG AS VARCHAR(200)=’BRUTE FORCE ATAK UYARISI:’

SET @MSG=@MSG+CONVERT(VARCHAR,@MINDATE,109)+’ ILE ‘+CONVERT(VARCHAR,@MAXDATE,109) +’ TARIHLERI ARASINDA ‘

SET @MSG=@MSG+CONVERT(VARCHAR,@COUNT)+’ KEZ LOGIN FAILED HATASI ALINDI’

SET @MSG=@MSG+’ CLIENT=’+@CLIENT

INSERT  INTO AUDIT.dbo.BLACKLIST

(

COMPUTERIP ,

DATE_ ,

MINDATE ,

MAXDATE ,

ERRORTEXT

)

VALUES  (

@CLIENT ,

GETDATE() ,

@MINDATE ,

@MAXDATE ,

@TEXT

);

EXEC msdb.dbo.sp_send_dbmail

@profile_name=”BIM”,

@recipients=’ocolakoglu@gmail.com’,

@subject = ‘Sisteminize 100 den fazla brute force şifre denemesi yapıldı’,

@body = @msg ,

@body_format = ‘HTML’ ;

END

DROP  TABLE #SQLErrorLog

Blacklist tablomuza kayıtlar şu şekilde atıldı.

Gelen mail ise bu şekilde.

Şimdi sıra geldi blackliste düşen ip lerin sisteme girişinin engellenmesi. Saldırgan şifreyi kırsa bile içeri giremeyecek ve şifreyi kırdığının farkına varmadan saldırmaya devam edecek.

Bunun için bir logon trigger yazıyoruz. Yalnız saldırıyı kendi makinemizden yapıyorsak bu kodu koyduğumuzda tekrar kendimiz de bağlanamayabiliriz. O yüzden bundan sonraki saldırıları başka bir makineden yapmak gerekiyor ya da master database i mizin bir yedeğini almakta fayda var.

Yazdığımız logon trigger aşağıdaki gibi.

USE [master]

GO

CREATE TRIGGER BRUTE_FORCE_DISABLE_CONNECTION

ON ALL SERVER

FOR LOGON

AS

IF CONVERT(VARCHAR,CONNECTIONPROPERTY (‘client_net_address’)) NOT IN (SELECT COMPUTERIP FROMAUDIT.DBO.BLACKLIST)

BEGIN

ROLLBACK

END

GO

Şimdi tekrar saldırıyoruz. 179. Sırada bizim şifremizi bulması gerekirken bulamadan atlayacak ve diğer şifreleri denemeye devam edecek. Böylece yazının en başında bahsettiğimiz gibi biz saldırganı hem engellemiş hem de iş üstünde yakalamış olacağız.

Aşağıdaki görüntüde görüldüğü gibi sistem 561 kez denemesine rağmen halen bulamamıştır. Oysa saldırgan şifreyi daha 179 uncu denemede bulmuştu. Ancak bulduğunu farkedemedi.

SQL Server’da Change Data Capture ile Değişen ve Silinen Kayıtların Loglanması

SQL Server Change Data Capture ile Değişiklik Yapılan Kayıtların Loglanması

Bu makalemizde SQL Server tarafında yapılan maniplasyonların (Insert, Update, Delete) geri planda otomatik olarak kayıt altına alınmasını anlatıyor olacağız.
Şimdi bir senaryo düşünelim. Bir ticari yazılımımız var. Bu yazılımı dışarıdan satınaldık ve özelliklerine müdahele edemiyoruz kaynağı bizde olmadığı için.
Sistem üzerinde önemli bir fatura hareketinin değiştirildiğini ya da çıkarıldığını düşünelim. Son dönemlerdeki ticari yazılımlar bunların kayıt altına alınmasına izin veriyor ancak vermeyenler de var. Bu anlamda bizim database bazında bu kayıtların loglanmasına ihtiyacımız söz konusu.
Bu işlerle biraz uğraşanlar için ilk akla gelen tabiki trigger yazılması. Doğru bu bir çözümdür ancak sıkıntıları vardır.
Bu sıkıntılar genel olarak şöyledir;
  • Sizin yazdığınız trigger ticari programın kendisinin hata vermesine sebep olabilir ve kayıtların yapılmamasına sebep olabilir. Zira trigger lar transactionların bir parçasıdır ve trigger da gerçekleşen hata tüm transaction ı rollback  yapar.
  • Özellikle mevzuat değişimi gereği sıklıkla versiyon geçişi söz konusudur ve bu versiyon geçişlerinde database düzenlemesi yapıldığı için büyük ihtimal trigger larınız silinir ve her seferinde yeniden oluşturacak scriptler oluşturmanız gerekecektir.
  • Genel olarak Türkiye şartlarında dönem mali dönem bağımlı çalışmak tercih edildiği için her yıl başında fiziken yeni tablolar oluşturulmaktadır ve bunlar için de trigger lar yeniden yazılmalıdır.
Anlaşılacağı üzere trigger meselesi etkin bir çözümdür fakat biraz zahmetlidir.
Peki bizim yazımızın da konusu olan bu durum için bir çözüm yok mu? Birim fiyatı 5000 TL olan bir malzemenin satış faturasındaki fiyatını 50 TL olarak  değiştiren bir kişiyi tespit etmenin pratik bir yolu yok mudur?
Bu noktada imdadımıza SQL Server Change Data Capture (CDC) dediğimiz özellik yetişiyor. Bu arkadaş yetenekli bir arkadaş. SQL Server’da bildiğiniz üzere tüm manipülasyon işlemleri önce Log dosyasına sonra Data dosyasına yazılır.  Burada log dosyası diye bahsettiğim SQL server’ın sistem log dosyası değil database’in Log dosyasıdır (LDF).
İşte CDC sistem üzerinde Log dosyasını izler ve olan değişiklikleri hızlı bir şekilde kayıt altına alır.
Örnek olarak siz aşağıdaki gibi bir UPDATE cümlesi çalıştırdınız.
UPDATE CUSTOMERS SET ACTIVE=1
CUSTOMERS tablosunun 20 alandan oluştuğunu varsayalım oysa biz sadece bir alanı update ettik. Dolayısıyla SQL Server transaction log üzerinde sadece bir alanlık işlem hacmi söz konusu.
İşte Change Data Capture sadece bu bilgiyi okuyarak arka planda veriyi logluyor.
Siz  CDC yi configure ederken belli bir süreliğine dataları loglayıp belli bir tarihten öncesini sildirebiliyorsunuz. Burada yazacağınız bir script ile önce bu datalara herhangi bir warehouse ortamına alıp daha sonra sistemden temizleyebilirsiniz.
sql-data-capture-1.jpg
Öncelikle şunu başta belirtmek isterim ki bu özellik SQL Server 2008 den beri vardır ancak Enterprise edition üzerinde çalışır. Tabi test ortamları için developer edition da enterprise ın tüm özelliklerine sahiptir.
Şimdi bu CDC nasıl çalışıyor bir bakalım.
1. Önce bir tablo oluşturalım.
CREATE TABLE dbo.Customers(ID int Primary Key NOT NULL,Name varchar(100) NOT NULL,Address varchar(500) NOT NULL)
2.Database imizde CDC yi enable yapıyoruz.
EXEC sp_cdc_enable_db
3.Tablomuzda CDC yi enable yapalım.
EXEC sp_cdc_enable_table @source_schema = N’dbo’,@source_name = N’Customers’,@role_name = NULL,@filegroup_name =N”,@supports_net_changes = 1
CDC yi enable ettikten sonra system tables altında aşağıdaki tablolar oluşur.
sql-data-capture-2.jpg
  • cdc.captured_columns : Adından da anlaşılacağı üzere değişikliklerin takip edileceği kritik alanları tutar. Bu tablo manuel olarak edit edilebilir durumda olup içeriği değiştirilebilir.
  • cdc.change_tables :Hangi tabloların değişiminin takip edileceği bilgisini tutar.
  • cdc.ddl_history :Şema bilgilerindeki değişiklikleri tutar.
  • cdc.lsn_time_mapping: Asıl Tablo üzerinde yapılan her transaction işlemi bu tablo içerisinde tutulur ve içerisindeki lsn bilgisine göre hangi sırada yapıldığı bilgisi tutulur.
4.Şimdi bir kayıt ekleyelim.
       INSERT INTO CUSTOMERS (Id,Name,Address) VALUES  (1,’ÖMER’,’TÜRKİYE’);
       INSERT INTO CUSTOMERS (Id,Name,Address) VALUES  (1,’AHMET’,’İSTANBUL’)
5.UPDATE yapalım.
               UPDATE CUSTOMERS SET ADDRESS=’ANKARA’ WHERE ID=1
6.DELETE Yapalım
       DELETE FROM CUSTOMERS WHERE ID=1
Görüldüğü gibi tablo üzerinde 2 insert,1 update ve 1 delete işlemi yaptık. Burada sistemde 4 satır kaydın logunun tutulması gerekiyor. Bakalım görebilecek miyiz?
Şimdi tablolarımıza bir bakalım

 

LbLmvo.png

 

CDC.ddl_history tablosu
sql-data-capture-6.jpg
CDC.index_column tablosu
sql-data-capture-7.jpg
CDC.lsn_time_mapping tablosu
Sistemde loglanan kayıtları ya doğrudan ya da tarih parametresi alan table valued function lar ile görebiliyoruz. Bu tablodaki kayıtlar ise log sequence number (lsn) ile tutuluyor. Bu fonksiyonlar da yine tablo bazlı olarak otomatik oluşuyor. Aşağıdaki resimde bu fonksiyonları görebilirsiniz.
sql-data-capture-8.jpg
Log kayıtlarını ulaşmak  istediğimizde  eğer tablonun tamamına ulaşmak istiyor isek
select
* from cdc.dbo_customers_CT şeklinde kullanıyoruz.
burada tablo formatı cdc.<schema>_<tablename>_CT şeklinde.
Bunun kullanımını sonucu aşağıdaki gibi.
sql-data-capture-9.jpg
table valued functionlar  ise aşağıdaki gibi kullanılıyor.
DECLARE @from_lsn binary(10), @to_lsn binary(10);
–minimum lsn numarasını buluyoruz.
SET @from_lsn = sys.fn_cdc_get_min_lsn(‘dbo_customers’);
–maximum lsn numarasını buluyoruz.
SET @to_lsn = sys.fn_cdc_get_max_lsn();
— CDC ile ilgili işlemlerde tablo bazlı oluşan cdc function larını kullanıyoruz.
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customers(@from_lsn, @to_lsn, ‘all’);
sql-data-capture-10.jpg
Görüldüğü üzere sistem 4 adet fazladan alan ve sistemde yapılan değişiklik üzerine loglanan kayıtları getirdi.
Burada
__$start_lsn log: sequence number bilgisini içeriyor. Buradan kayıt tarihine erişebiliyoruz.
__$seqval: Sequnce değeri yani işlemin hangi sırada gerçekleştiği bilgisine erişmek için bu alan kullanılıyorç
__$operation:2 Insert, 4 Update ve 1 Delete için kullanılıyor.
__$operation:1 Insert,Delete 0 Update
anlamına gelmektedir.
Burada kayıt zamanını elde etmek istediğimizde
sys.fn_cdc_map_lsn_to_time function ını kullanıyoruz.
select sys.fn_cdc_map_lsn_to_time(__$start_lsn) as KayitZamani,
* from cdc.dbo_customers_CT
sql-data-capture-11.jpg
Burada oluşan log kayıtlarını temizlemek için ise
sp_cdc_cleanup_change_table
komutunu kullanıyoruz.
Kullanımı aşağıdaki gibi.
— aşağıdaki kod 3 gün öncesine ait logları temizliyor.
declare @lsn binary(10);
set @lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,getdate()-3);
exec sys.sp_cdc_cleanup_change_table @capture_instance = ‘dbo_Customers’, @low_water_mark=@lsn

–CDC yi disable etmek için ise
sp_cdc_disable_db,
sp_cdc_disable_table
komutları kullanlr

EXECUTE sp_cdc_disable_table@source_schema = N’dbo’,@source_name = N’Customers’,@capture_instance=N’dbo_Customers’
CDC çalıştırabilmek için SQL Server agent a ihtiyacımız söz konusu.  Sistem 2 adet job ı otomatik olarak oluşturmaktadır. Bunlardan birisi değişen datanın capture edilmesini sağlarken diğeri de logları temizlemektedir.
sql-data-capture-12.jpg
Sonuç:
  1. CDC gerçekten çok ihtiyaç duyulan ve çok kullanışlı bir araç.
  2. Sistemdeki insert, update ve delete leri loglayabiliyor.
  3. Eğer update cümlesinde kayıt değişmiyor ise gereksiz yer teşkil etmiyor.
  4. Örneğin: UPDATE CUSTOMERS SET NAME=NAME cümlesini çalıştırdığımızda herhangi bir loglama yapmıyor çünkü değişen bir şey yok.
  5. Sistemin çalışıyor olması için SQL Server Agent’ın mutlaka çalışması gerekir. Çünkü logları okuyan bir job bu işleri yerine getirmektedir.
  6. Yazacağımız bir script ile istediğimiz tablolarda çalıştırıp istemediklerimizde çalıştırmayabiliriz. Hatta çok fazla kolon olan bir tabloda istediğimiz kolonlar için aktif hale getirirken istemediklerimizi es geçebiliriz.