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

भाग 2: रिपोर्ट

नमस्कार, पहले भाग में, हमने एक तालिका बनाई जो नियमित रूप से प्रति घंटे एक बार डेटा के साथ अपडेट की जाती है।
अब, उदाहरण के लिए, हम कई रिपोर्ट बनाएंगे (मैंने रिपोर्ट बनाने के लिए रिपोर्ट बिल्डर 2.0 का उपयोग किया)।

वार्म अप करने के लिए, हम कुल ट्रैफ़िक पर एक रिपोर्ट बनाएंगे जो एक निश्चित समय के लिए टीएमजी से होकर गुज़रे:

use tmg select (SUM(bytesrecvd)/1024)/1024 as ' ', (sum(bytessent)/1024)/1024 as ' ', ((SUM(bytesrecvd) + sum(bytessent))/1024)/1024 as ' ' from dbo.report where logTime >= @FromDate AND logTime <= @ToDate 

उस समय की लंबाई का चयन करने के लिए जिसके लिए आप एक रिपोर्ट बनाना चाहते हैं, मैंने चर बनाए @FromDate और @ToDate (बिल्डर 2.0 में चर पैरामीटर अनुभाग में बनाए गए हैं, चर का प्रकार दिनांक (समय, डिफ़ॉल्ट मान सेट नहीं है)।

यह बिल्डर जैसा दिखता है:


इस तरह तैयार रिपोर्ट दिखती है:


अब कंपनी के कुछ विभाग द्वारा इंटरनेट ट्रैफ़िक की खपत पर एक रिपोर्ट बनाते हैं। उपयोगकर्ताओं को विभाग से बाँधने के लिए, मुझे संबंधित विभाग के OU में सभी sAMAccountName की खोज करनी थी (AD से अनुरोध करने के लिए, डोमेन नियंत्रक में से एक पर एक लिंक्ड सर्वर बनाया गया था)।

एक अनुरोध करें:

 use tmg declare @tbl table(name varchar(256)) insert @tbl select '< >\' + sAMAccountName from openquery ( ADSII,'SELECT sAMAccountName FROM ''LDAP://< >'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ') select clientusername, (SUM(bytessent)/1024)/1024 as ' M', (sum(bytesrecvd)/1024)/1024 as ' ', ((SUM(bytesrecvd) + sum(bytessent))/1024)/1024 as '   ' from dbo.report where ClientUserName in ( SELECT name from @tbl) and (logTime >= @FromDate AND logTime <= @ToDate) group by clientusername 

यहां यह ध्यान देने योग्य है कि विज्ञापन से क्वेरी परिणामों की रिकॉर्डिंग के लिए एक अस्थायी तालिका का उपयोग, यह रिपोर्ट के प्रदर्शन में काफी सुधार कर सकता है। रिपोर्ट का उदाहरण:

इस रिपोर्ट में, उपयोगकर्ता नाम एक अन्य रिपोर्ट के लिंक के रूप में कार्य करता है जिसमें ये नाम, और टॉडेट, टॉडेट को पैरामीटर के रूप में पारित किया जाता है, हम इस रिपोर्ट पर बाद में विचार करेंगे।

उपयोगकर्ता रिपोर्ट में, हम इस बात में रुचि रखते हैं कि उसने किन साइटों पर दौरा किया और एक निश्चित अवधि में उसने उनसे कितना ट्रैफ़िक डाउनलोड किया, हम एक अनुरोध लिखते हैं:

 use tmg select top (30) percent ClientUserName, destinationhost,(SUM(bytesrecvd)/1024)/1024 as ' ', (sum(bytessent)/1024)/1024 as ' M', ((SUM(bytesrecvd) + sum(bytessent))/1024)/1024 as 'total' from (select CASE WHEN ISNUMERIC(replace(destinationhost, '.', '') )=1 THEN destinationhost ELSE dbo.ParseUrl(destinationhost) END destinationhost, bytesrecvd, bytessent, clientusername, logtime from dbo.report )report2 where (clientusername like @Name) and (logTime >= @FromDate AND logTime <= @ToDate) group by clientusername, destinationhost order by total desc 

इस अनुरोध में, अन्य बातों के अलावा, हम जाँचते हैं कि क्या डेस्टहोस्ट एक एफक्यूडीएन है, यदि ऐसा है तो हम इसे पार्सुरल फ़ंक्शन का उपयोग करके पार्स करते हैं, और उसके बाद ही रिपोर्ट में सम्मिलित करते हैं।

Parseurl समारोह:

 USE [TMG] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[parseURL] ( @url varchar(128) ) RETURNS varchar(128) AS BEGIN declare @s varchar(128), @i int IF (@url is null) RETURN @url SET @s = REVERSE(@url) SET @i = CHARINDEX('.', @s) IF (0 = @i) RETURN @url SET @i = CHARINDEX('.', @s, @i + 1) IF (0 = @i) RETURN @url RETURN REVERSE(SUBSTRING(@s, 1, @i - 1)) END GO 

मेरे मामले में, दूसरे स्तर से नीचे के उप-डोमेन रिपोर्ट के लिए दिलचस्प नहीं हैं, और उपरोक्त फ़ंक्शन उन्हें दूसरे स्थान पर ले जाता है:


प्रदर्शन में सुधार के लिए, मैं एक गैर-संकुल सूचकांक बनाने की सलाह देता हूं, जो मेरे प्रश्नों के लिए एक उदाहरण है:

 USE [TMG] GO CREATE NONCLUSTERED INDEX [dateindex2] ON [dbo].[REPORT] ( [logTime] ASC ) INCLUDE ( [ClientUserName], [bytesrecvd], [bytessent]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 

निष्कर्ष:
1 - "स्वच्छ" तालिका का उपयोग करने से आप संग्रहीत डेटा की मात्रा को कम कर सकते हैं और रिपोर्ट बनाने में लगने वाले समय को कम कर सकते हैं।
2 - एसक्यूएल नौकरी, जो एक साफ टेबल पर डेटा लिखती है, औसतन 30 सेकंड चलती है।
3 - रिपोर्ट 5 मिनट से अधिक नहीं बनी हैं।
4 - रिपोर्ट तैयार करने के लिए सिस्टम एडमिनिस्ट्रेटर को शामिल करना आवश्यक नहीं है

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