SQL-Server2005で、誰がログインしてきたのか、って知りたくない？
でもいちいち監視してたり、Profilerで見ているわけにもいかなくて、ただ、ログ情報として貯めておいて、後で解析したいなぁなんて思うこともあると思うの。そんなときの方法についてちょぴっとｗ
SQL-Serverには、イベントが発生したらそれを記録する機能がついてることが判明。ServiceBrokerというらしい。この怪しい？ブローカーに「一名はいりまーす！」と連絡来たら記録をとるように指示してしまえばいいわけですね
CREATE QUEUE TEST_Queue ;
GO
CREATE SERVICE TEST_service
on queue dbo.TEST_Queue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
CREATE EVENT NOTIFICATION TEST_LOGIN
on server for AUDIT_LOGIN,AUDIT_LOGOUT,AUDIT_LOGIN_FAILED TO SERVICE 'TEST_service','current database';
GO
alter queue dbo.TEST_Queue with status=on
こんなカンジ。これで、AUDIT＿LOGIN関係イベントがサーバーに起きると勝手にQUEUEにためて行ってくれるのさ。で、色々といじったり何やかんやしてから、実際に覗いてみる、と。
で、さっそく selectしてみる。情報はmessage_body列に入ってるってことだけど……
select message_body from TEST_QUEUE
？？？？？
なんだこれ？
実はmessage_bodyはXML型。しかも人には読めないようにシリアルバイナリ化。読み出すには牛乳かけてテクが必要
まずは、XML型の読み出し先専用一時テーブルを作って
create table #QueueTest(msg XML);
ここに、データをINSERT
insert into #QueueTest select message_body from TEST_QUEUE;
元のmessage_bodyはシリアル化されたバイナリ値なんだけど、一度このテーブルを介するとなんと文字列になるんだね♪
select * from #QueueTest
XMLデータレコードが出現！イベントの発生日時と内容が記載されてますので、後はこれを好きに食え（笑）
drop table #QueueTest;
create table #QueueTest(msg XML);
insert into #QueueTest select message_body from SCGAUGE_Queue;
select
msg.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(30)') as DB,
msg.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(128)') as VARB
from #QueueTest;
alter queue dbo.SCGAUGE_Queue with status=off;
ま、ここまでXMLで取れたんだし、どうせならWhere句で色々と遊んでみる
HOGEデータベースで起きたイベントだけ取る
where msg.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(30)') = 'HOGE'
insert文だけ取ってみる
where msg.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(30)') like 'insert%'
今日のだけ取ってみる
where DATEPART(month,cast(msg.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(128)') as datetime)) = DATEPART(month, GETDATE())
AND DATEPART(day,cast(msg.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(128)') as datetime)) = DATEPART(day, GETDATE())
なーんて、テキトーにｗ
参考になったネタはここ。XMLデータ型列から値を取り出したりする部分の具体的な例文が出てました。ありがたや。
http://www.15seconds.com/issue/050803.htm
で、こいつを使うと
http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319
こういうこともできるらしいｗ
メールでブログを更新したり、とかいうのもこのテクノロジで可能なわけだね。
とりあえず、調査はここまで～