एमएस रिपोर्टिंग सेवाओं के आधार पर इंटरनेट ट्रैफिक TMG पर रिपोर्ट का निर्माण

1 हिस्सा: साफ टेबल

किसी समय में, मुझे एक लचीली एंड-यूज़र ट्रैफ़िक रिपोर्टिंग प्रणाली बनाने की आवश्यकता थी। यह प्रणाली दो समस्याओं को हल करने वाली थी: सिस्टम प्रशासकों पर लोड को कम करना, उपयोगकर्ताओं को सुविधाजनक वास्तविक समय रिपोर्टिंग प्रणाली प्रदान करना।

कार्य की प्रारंभिक शर्तें: एक टीएमजी सर्वर है जो लॉग को दूरस्थ एमएस SQL ​​सर्वर पर लिखता है।

इसलिए, यदि आप उस डेटाबेस को खोलते हैं जिसमें TMG लॉग्स लिखे गए हैं, तो हम दो तालिकाओं को देखेंगे - फायरवालॉग और वेबप्रोडलॉग। रिपोर्ट बनाने और संग्रहीत डेटा की मात्रा का अनुकूलन करने के लिए, मैंने एक तीसरा, "स्वच्छ", तालिका - रिपोर्ट बनाई:

CREATE TABLE [dbo].[report]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [ClientUserName] [nvarchar](514) NULL, [DestinationHost] [nvarchar](514) NULL, [bytesrecvd] [bigint] NULL, [bytessent] [bigint] NULL, [logTime] [date] NULL, [ClientAgent] [varchar](255) NULL, [ClientIP] [nvarchar](514) NULL, CONSTRAINT [PK_report] PRIMARY KEY CLUSTERED 

Firewalllog और Webproxylog तालिकाओं का डेटा इस तालिका में स्थानांतरित कर दिया जाएगा। डेटा ट्रांसफर करने के लिए, मैंने SQL जॉब का इस्तेमाल किया, जो एक घंटे में एक बार चलती है और डेटा को समेटती है, उसे एक क्लीन टेबल में डालती है, फिर इस डेटा को डिफॉल्ट टेबल से डिलीट कर दिया जाता है, और 180 दिनों से अधिक पुरानी "क्लीन" टेबल के डेटा को भी डिलीट कर दिया जाता है:

 insert into dbo.REPORT (ClientUserName, ClientAgent, clientip, logtime, destinationhost, bytesrecvd, bytessent) select dbo.GetUserName (ClientUserName, SourceIP) as ClientUserName, ClientAgent, dbo.parseip (sourceip) as clientip, cast (logtime as date) as logtime, dbo.parseip (destinationip) as destinationhost, SUM(bytessent) as bytessent, sum(bytesrecvd) as bytesrecvd from dbo.FirewallLog where (DestinationIP not like '  ' and DestinationIP not like '  TMG' and SourceIP not like '  TMG' ) and bytessent + bytesrecvd <> 0 and (cast (logtime as date) >= cast (dateadd (day, -5, GETDATE()) as DATE) and cast (logtime as date) <= cast (GETDATE() as DATE)) and Action <> 11 group by ClientUserName, SourceIP, DestinationIP, cast (logtime as date), ClientAgent union select dbo.GetUserName (ClientUserName, ClientIP) as ClientUserName, ClientAgent, dbo.parseip (clientip) as clientip, cast (logtime as date) as logtime, urldesthost as destinationhost, SUM(bytesrecvd) as bytessent, sum(bytessent) as bytesrecvd from dbo.WebProxyLog where (DestHostIP not like '  ' and DestHostIP not like '  TMG' and ClientIP not like '  TMG') and bytessent + bytesrecvd <> 0 and (cast (logtime as date) >= cast (dateadd (day, -5, GETDATE()) as DATE) and cast (logtime as date) <= cast (GETDATE() as DATE)) and Action <> 11 group by ClientUserName, ClientIP, UrlDestHost, cast (logtime as date), ClientAgent delete from dbo.FirewallLog where cast (logtime as date) >= cast (dateadd (day, -5, GETDATE()) as DATE) and cast (logtime as date) <= cast (GETDATE() as DATE) delete from dbo.WebProxyLog where cast (logtime as date) >= cast (dateadd (day, -5, GETDATE()) as DATE) and cast (logtime as date) <= cast (GETDATE() as DATE) delete from dbo.REPORT where logtime <= cast (dateadd (day, -180, GETDATE()) as DATE) 

हम अनुरोध को और अधिक विस्तार से विश्लेषित करेंगे, सबसे पहली चीज जो हमें करनी थी वह है डेटा फॉर्मेट जिसमें IP पता, स्रोत और गंतव्य फ़ील्ड शामिल हैं। इन फ़ील्ड्स में एक अद्वितीय डेटा प्रकार होता है, और IPv4 पते में C0A89E4B-FFFF-0000-0000-000000000000 रूप होता है। इस स्ट्रिंग को सामान्य IPv4 पते में बदलने के लिए, आपको FFFF से पहले इस स्ट्रिंग के बाईं ओर ले जाने की आवश्यकता है, इसे दो अंकों के 4 खंडों में विभाजित करें और हेक्साडेसिमल से दशमलव में परिवर्तित करें - C0.A8.9E.4.1 = 192.168.158.75। इस प्रयोजन के लिए, क्वेरी dbo.parseip फ़ंक्शन का उपयोग करती है:

 USE [TMG] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[parseIP] ( @ui uniqueidentifier ) RETURNS varchar(128) AS BEGIN declare @hex varchar(8), @t varchar (8), @number int = 1, @n INT = 0, @IP varchar(128) = '', @i int = 1 SET @hex = SUBSTRING(CONVERT(VARCHAR(128), @ui), 1, 8) WHILE @i <8 BEGIN set @t = REVERSE(SUBSTRING(@hex, @i, 2)) --SELECT @t WHILE @number < = LEN(@t) BEGIN SET @n = @n + case lower(SUBSTRING(@t, @number, 1)) when '0' then 0 when '1' then 1 when '2' then 2 when '3' then 3 when '4' then 4 when '5' then 5 when '6' then 6 when '7' then 7 when '8' then 8 when '9' then 9 when 'a' then 10 when 'b' then 11 when 'c' then 12 when 'd' then 13 when 'e' then 14 when 'f' then 15 end * convert( decimal( 28 , 0 ) , power( 16 , @number - 1 ) ) SET @number = @number + 1 END -- SELECT @n SET @IP = @IP + CASE WHEN LEN(@IP) >0 THEN '.' ELSE '' END + CONVERT(VARCHAR(3), @n) SELECT @n = 0, @number = 1 SET @i = @i +2 END --SELECT @IP -- Return the result of the function RETURN @IP END 

अगला dbo.GetUserName फ़ंक्शन है:

 USE [TMG] GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE FUNCTION [dbo].[GetUserName] (@Username nvarchar(128), @IpAddress uniqueidentifier) RETURNS nvarchar(128) AS BEGIN IF (@Username = NULL OR @Username = '-' OR @Username = 'anonymous') RETURN dbo.parseip (@IpAddress); RETURN @Username; END 

यहां कुछ भी विशेष रूप से दिलचस्प नहीं है, फ़ंक्शन का उपयोग आईएसए 2006 में वापस किया गया था, केवल एक चीज जिसे बदलना पड़ा था वह @IpAddress के लिए डेटा प्रकार था।

इस क्वेरी में सभी कार्य हैं, अब डेटा रिकॉर्डिंग की कुछ बारीकियों को देखते हैं।

Webproxylog तालिका में, स्तंभों और bytesrecvd का अर्थ उनके नामों के बिल्कुल विपरीत है, इसलिए क्वेरी में उन्हें उल्टा कर दिया जाता है - SUM (bytesrecvd) bytessent, sum (bytessent) को bytesrecvd के रूप में।
आखिरी चीज जो मैं नोट करना चाहूंगा वह है कनेक्शन और अशक्त रिकॉर्ड्स के मध्यवर्ती राज्य को फ़िल्टर करना। तथ्य यह है कि "दीर्घकालिक" कनेक्शन के साथ, TMG ट्रैफ़िक की मध्यवर्ती मात्रा को रिकॉर्ड करता है जो कनेक्शन प्राप्त / प्रेषित होता है, इस तरह के मध्यवर्ती डेटा को एक्शन <> 11. का उपयोग करके फ़िल्टर्ड किया जाता है। शून्य रिकॉर्ड के संबंध में, सब कुछ स्पष्ट रूप से पर्याप्त है, वे पिछले ट्रैफ़िक की गिनती के लिए कोई मूल्य नहीं हैं।
इस अनुरोध को निष्पादित करने के बाद, डेटा "क्लीन" टेबल पर दिखाई देता है, जिसके आधार पर रिपोर्टिंग सेवाओं में रिपोर्ट का निर्माण किया जाता है, लेकिन दूसरे भाग में उस पर अधिक।

पुनश्च: एसक्यूएल सवालों के जवाब देने के लिए, और पार्सिप प्रक्रिया लिखने के लिए मेरे सहयोगियों का धन्यवाद।