هذا هو إصدار SQL لتقرير لوحة المعلومات لتسجيلات الدخول المشبوهة.
يوفر تقرير لوحة المعلومات هذا تفاصيل حول تسجيلات الدخول الجديدة من المستخدمين التي تختلف بشكل مشبوه عن تسجيلات الدخول السابقة، خلال نطاق تاريخي محدد. من خلال تحديد محاولات تسجيل الدخول غير العادية، يمكن للمسؤولين اتخاذ تدابير استباقية لتأمين حسابات المستخدمين والمنتدى ضد التهديدات الأمنية المحتملة.
-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08
SELECT
u.id user_id,
u.username,
t.client_ip,
t.user_agent,
t.created_at login_time
FROM user_auth_token_logs t
JOIN users u ON u.id = t.user_id
WHERE t.action = 'suspicious'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
ORDER BY t.created_at DESC
شرح استعلام SQL
يعمل استعلام SQL عن طريق استرداد قائمة بمحاولات تسجيل الدخول التي تم تمييزها على أنها مشبوهة ضمن نطاق تاريخي محدد. إليك تفصيل لكيفية عمل الاستعلام:
- تعريف المعلمات: يبدأ الاستعلام بتعريف معلمتين،
:start_dateو:end_date، لتحديد الفترة الزمنية للتقرير. تقبل كلتا معلمتي التاريخ تنسيقYYYY-MM-DD. - اختيار البيانات: يختار الاستعلام حقولًا محددة من جدول
user_auth_token_logs، الذي يسجل تفاصيل حول أنشطة مصادقة المستخدم، وجدولusers، الذي يحتوي على معلومات حول المستخدمين أنفسهم. تشمل الحقول المختارة:u.id(user_id): المعرف الفريد للمستخدم.u.username: اسم المستخدم.t.client_ip: عنوان IP الذي تمت منه محاولة تسجيل الدخول.t.user_agent: سلسلة وكيل المستخدم للمتصفح أو الجهاز المستخدم في محاولة تسجيل الدخول.t.created_at(login_time): الطابع الزمني لوقت حدوث محاولة تسجيل الدخول.
- عملية الربط: يربط الاستعلام جدول
user_auth_token_logs(t) بجدولusers(u) على حقلuser_idالمشترك لربط كل محاولة تسجيل دخول بمعلومات المستخدم المقابلة. - التصفية: يقوم بتصفية السجلات لتشمل فقط محاولات تسجيل الدخول التي تم تمييزها على أنها
suspiciousفي حقلactionبجدولuser_auth_token_logs. بالإضافة إلى ذلك، يقوم بتصفية السجلات لتشمل فقط تلك التي حدثت ضمن النطاق التاريخي المحدد (من:start_dateإلى:end_date). - الترتيب: أخيرًا، يقوم الاستعلام بترتيب النتائج بترتيب تنازلي حسب الطابع الزمني
created_at، مما يضمن إدراج أحدث محاولات تسجيل الدخول المشبوهة أولاً.
نتائج مثال
| user | username | client_ip | user_agent | login_time |
|---|---|---|---|---|
| user_id_example_1 | username_example | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | Mozilla/5.0 (iPhone; CPU iPhone OS 17_2_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.2 Mobile/15E148 Safari/604.1 | 2024-01-09T08:18:57.535Z |
| … | … | … | … | … |