آمار بازدید و استفاده کاربران از اسناد و صفحات در شیرپوینت Statistics of user visits in SharePoint

تحلیل آمار بازدید و استفاده از اسناد در محیط شیرپوینت می‌تواند اطلاعات ارزشمندی در مورد تعاملات کاربران با شیرپوینت ارایه دهد؛ در این مقاله می‌آموزیم چگونه یک کوئری داینامیک SQL برای استخراج آمار بازدید از دیتابیس شیرپوینت ایجاد کنیم و یک دشبورد پاور بی‌آی برای مصورسازی این داده‌ها بسازیم.

پیگیری تعاملات کاربران با اسناد و صفحات می‌تواند به شما کمک کند:

• اسناد و صفحات پرکاربرد و پربازدید را شناسایی کنید.
• محتوا را بهینه کنید و تعامل کاربران را بهبود بدهید.

مرحله ۱: یافتن محل ذخیره‌سازی آمار بازدید در دیتابیس

برای شروع، باید مشخص کنیم که گزارش‌های لاگ فعالیت کاربران، کجا ذخیره می‌شود؛ برای انجام این کار مراحل زیر را انجام دهید:

• Central Administration را باز کنید.
• از منوی سمت چپ روی Monitoring کلیک کنید.
• وارد Configure usage and health data collection شوید.
• در بخش Logging Database Server سرور دیتابیس و نام دیتابیس را مشاهده کنید.
• این اطلاعات را جایی یادداشت کنید.

معمولاً نام دیتابیس چیزی شبیه WSS_UsageApplication است.

با استفاده از نرم‌افزار  SQL Server Management Studioیا SSMS دیتابیسی که یادداشت کرده‌اید را باز کنید و بررسی کنید که آیا در آن جداولی مانند RequestUsage_Partition وجود دارد؟ این جداول چون معمولاً شامل لاگ‌های حجیم هستند، داده‌ها را به شکل پارتیشن‌بندی‌شده بر اساس بازه‌های زمانی ذخیره می‌کنند تا از نظر پرفورمنس بهینه‌تر باشند.

مثلاً اگر قرار است فقط آمار بازدید یک هفته ذخیره شوند، اطلاعات از روز اول تا روز هفتم به صورت جداگانه در پارتیشن های 01 تا 07 ذخیره می‌شوند و در روز هشتم، پارتیشن 01 خالی می‌شود و اطلاعات روز هشتم در آن قرار می‌گیرد.

اکنون که پایگاه داده و جدول‌های مربوط به ذخیره‌سازی آمار بازدید و سایر داده‌ها را شناسایی کردیم، می‌توانیم به مرحله بعدی برای نوشتن کوئری برویم.

مرحله ۲: ساختار کوئری

کوئری SQL زیر اطلاعات مربوط به تاریخ بازدید، جزئیات سایت، مسیر اسناد و نام کاربری بازدیدکنندگان را از میان چندین پارتیشن دیتابیس استخراج می‌کند؛ در این کوئری برای مشخص کردن صفحات بازدید شده چک می‌کنیم که ستون DocumentPath شامل عبارت aspx باشد و برای مشخص کردن اسناد بازدید شده از چک می‌کنیم که ستون DocumentPath شامل عبارت‌های pdf و docx و xslx و pptx باشد؛ این چهار عبارت به ترتیب مرتبط با اسناد پی‌دی‌اف، وُرد، اکسل و پاورپوینت هستند؛ اگر نیاز به اسناد با فرمت‌های دیگری داشته باشید، باید فرمت‌های مورد نظر خود را به کوئری خود اضافه کنید.

به غیر از فیلتر کردن داده‌ها بر اساس فرمت، در کوئری فقط لاگ‌هایی که فیلد نام کاربری آن‌ها خالی نیست، انتخاب می‌شوند. ستون‌هایی که در کوئری استفاده کرده‌ایم بدین شرح هستند: VisitDate برای تاریخ بازدید، SiteId و SiteUrl برای سایت بازدید شده، DocumentPath برای مسیر فایل یا صفحه بازدید شده، Title برای عنوان سند یا صفحه و در نهایت UserLogin برای دریافت حساب کاربری بازدیدکننده:

DECLARE @sql NVARCHAR(MAX) = 'WITH CombinedData AS (';

DECLARE @i INT = 0;
DECLARE @end INT = 31;
WHILE @i <= @end

BEGIN
    SET @sql = @sql + '
    SELECT
        CAST(LogTime AS DATE) AS VisitDate,
        SiteId,
        SiteUrl,
        DocumentPath,
        Title,
        UserLogin
    FROM [DatabaseName].[dbo].[RequestUsage_Partition' + CAST(@i AS NVARCHAR) + ']
    WHERE
        (
            DocumentPath LIKE ''%.aspx%'' OR
            DocumentPath LIKE ''%.pdf%'' OR
            DocumentPath LIKE ''%.docx%'' OR
            DocumentPath LIKE ''%.xlsx%'' OR
            DocumentPath LIKE ''%.pptx%''
        )
    AND UserLogin IS NOT NULL';
    IF @i < @end SET @sql = @sql + ' UNION ALL ';
    SET @i = @i + 1;
END

SET @sql = @sql + ')

SELECT
    VisitDate,
    SiteId,
    SiteUrl,
    DocumentPath,
    Title,
    UserLogin
FROM CombinedData
ORDER BY VisitDate, UserLogin;';

EXEC sp_executesql @sql;

این اسکریپت از یک لوپ WHILE استفاده می‌کند تا به صورت داینامیک، اطلاعات بازدید کاربران را از پارتیشن‌ها دریافت کند؛ استفاده از چنین کوئری‌هایی در سیستم‌هایی که اطلاعات آن‌ها به صورت پارتیشنی ذخیره می‌شوند رایج است؛ می‌توانید از این کوئری در SSMS استفاده کنید و مطمئن شوید که کوئری درست کار می‌کند.

مرحله ۳: وارد کردن نتایج در پاور بی‌آی

پس از این که از نتایج کوئری مطمئن شدید، نرم‌افزار پاور بی‌آی دسکتاپ را باز کرده و از طریق Get data و سپس SQL Server به دیتابیس خود متصل شوید. برای دریافت اطلاعات از دیتابیس باید در پنجره‌ای که باز می‌شود، نام سرور، نام دیتابیس و اسکریپت SQL را وارد کنید.

مرحله ۴: طراحی دشبورد

از Power Query Editor برای تمیز کردن داده‌ها و آماده‌سازی آن‌ها استفاده کنید و از این طریق داده‌ها را برای ایجاد یک دشبورد تعاملی آماده کنید؛ من برای این پرژه، ابتدا تاریخ بازدیدها را شمسی کردم و اسم داکیومنت‌ها و صفحات را از ستون DocumentPath استخراج کردم تا خواناتر باشند. پس از آماده شدن داده‌ها، Close & Apply را بزنید تا به محیط طراحی دشبورد بازگردید.

همان طور که در تصویر زیر می‌بینید از پنج ویژوال برای نمایش داده‌ها استفاده کرده‌ام:

• تعداد بازدید به تفکیک روز
• سایت‌های پُر بازدید
• اسناد پُر بازدید
• صفحات پُر بازدید
• کاربران بر اساس تعداد بازدید