Clever Geek Handbook

SQL Login / Logout summary, upon request

I have an audit log that stores events that happen in one or more connected applications throughout the system.

DECLARE @startDate DATETIME;
DECLARE @endDate DATETIME;
DECLARE @staffID INT;

SET @staffID = 4;

SELECT 
    @startDate = dbo.Date(COALESCE(@startDate, DATEADD(day, -1, GETUTCDATE()))),
    @endDate = dbo.EndOfDay(COALESCE(@endDate, GETUTCDATE()))

SELECT
    l.RecordedOn, s.FirstName + ' ' + s.LastName + ' (#' + CAST(l.StaffIDAffected AS VARCHAR(MAX)) + ')' AS StaffName,
    InOut = CASE WHEN (l.Type = 'AUS') THEN 'Sign In' WHEN (l.Type = 'AUSO') THEN 'Sign Out' END,
    a.Name AS ApplicationName, l.ApplicationID
FROM Logs l 
LEFT JOIN OtherDB.dbo.Staff s ON (s.ID = l.StaffIDAffected)
LEFT JOIN Applications a ON (l.ApplicationID = a.ID)
WHERE
    l.Type in ('AUS','AUSO') AND
    l.StaffIDAffected = @staffID AND
    l.RecordedOn BETWEEN @startDate AND @endDate
ORDER BY s.FirstName + ' ' + s.LastName, l.RecordedOn ASC

This returns the following data: RecordedOn StaffName InOut ApplicationName ApplicationID 2015-06-01 13:56:32.490 Joel Smith (#4) Sign In NULL 0 2015-06-01 14:05:02.900 Joel Smith (#4) Sign In NULL 0 2015-06-01 14:06:01.470 Joel Smith (#4) Sign Out NULL 0 2015-06-01 14:22:57.000 Joel Smith (#4) Sign In NULL 0 2015-06-01 14:23:04.170 Joel Smith (#4) Sign In NULL 0 2015-06-01 14:36:10.293 Joel Smith (#4) Sign In NULL 0 2015-06-01 14:47:38.993 Joel Smith (#4) Sign In NULL 0 2015-06-01 14:55:56.297 Joel Smith (#4) Sign In Admin Website 4 2015-06-01 14:56:33.107 Joel Smith (#4) Sign In Panel 2 2015-06-01 14:56:43.783 Joel Smith (#4) Sign Out NULL 0 2015-06-01 15:00:03.950 Joel Smith (#4) Sign In Panel 2 2015-06-01 15:06:33.403 Joel Smith (#4) Sign In Admin Website 4 2015-06-01 15:06:45.843 Joel Smith (#4) Sign Out Admin Website 4 2015-06-01 15:23:57.543 Joel Smith (#4) Sign In Panel 2

The format of what I want is below. I am concatenating null app names to General and concatenating null app ids to -1. (NULL values ​​will fade away over time, but for now they need to be handled somewhat gracefully.)

StaffName ApplicationName ApplicationID SignIn SignOut Joel Smith (#4) General -1 2015-06-01 13:56:32.490 NULL Joel Smith (#4) General -1 2015-06-01 14:05:02.900 2015-06-01 14:06:01.470 Joel Smith (#4) General -1 2015-06-01 14:22:57.000 NULL Joel Smith (#4) General -1 2015-06-01 14:23:04.170 NULL Joel Smith (#4) General -1 2015-06-01 14:36:10.293 NULL Joel Smith (#4) General -1 2015-06-01 14:47:38.993 2015-06-01 14:56:43.783 Joel Smith (#4) Admin Website 4 2015-06-01 14:55:56.297 NULL Joel Smith (#4) Panel 2 2015-06-01 14:56:33.107 NULL Joel Smith (#4) Panel 2 2015-06-01 15:00:03.950 NULL Joel Smith (#4) Admin Website 4 2015-06-01 15:06:33.403 2015-06-01 15:06:45.843 Joel Smith (#4) Panel 2 2015-06-01 15:23:57.543 NULL

Please note that it must separate the login / logout times by app name / app id.

This is what I have but is missing something :

DECLARE @startDate DATETIME;
DECLARE @endDate DATETIME;
DECLARE @staffID INT;

SET @staffID = 4;

SELECT 
    @startDate = dbo.Date(COALESCE(@startDate, DATEADD(day, -1, GETUTCDATE()))),
    @endDate = dbo.EndOfDay(COALESCE(@endDate, GETUTCDATE()))

SELECT
    StaffName,
    ApplicationName,
    ApplicationID,
    SignIn,
    SignOut
FROM 
( 
    SELECT
        l.RecordedOn, 
        s.FirstName + ' ' + s.LastName + ' (#' + CAST(l.StaffIDAffected AS VARCHAR(MAX)) + ')' AS StaffName,
        InOut = CASE WHEN (l.Type = 'AUS') THEN 'SignIn' WHEN (l.Type = 'AUSO') THEN 'SignOut' END,
        COALESCE(a.Name, 'General') AS ApplicationName, 
        COALESCE(l.ApplicationID, -1) AS ApplicationID
    FROM Logs l 
    LEFT JOIN OtherDB.dbo.Staff s ON (s.ID = l.StaffIDAffected)
    LEFT JOIN Applications a ON (l.ApplicationID = a.ID)
    WHERE
        l.Type in ('AUS','AUSO') AND
        l.StaffIDAffected = @staffID AND
        l.RecordedOn BETWEEN @startDate AND @endDate
    --ORDER BY s.FirstName + ' ' + s.LastName, l.RecordedOn ASC
) p
PIVOT
(
    MIN(RecordedOn)
    FOR InOut IN ([SignIn], [SignOut])
) pvt

This code returns the following data:

StaffName ApplicationName ApplicationID SignIn SignOut Joel Smith (#4) Admin Website 4 2015-06-01 14:55:56.297 2015-06-01 15:06:45.843 Joel Smith (#4) General 0 2015-06-01 13:56:32.490 2015-06-01 14:06:01.470 Joel Smith (#4) Panel 2 2015-06-01 14:56:33.107 NULL

What am I doing wrong? I am using SQL Server 2012/2014.

EDIT I have also tried without a dot with interesting results:

DECLARE @startDate DATETIME;
DECLARE @endDate DATETIME;
DECLARE @staffID INT;

SET @staffID = 4;

SELECT 
    @startDate = dbo.Date(COALESCE(@startDate, DATEADD(day, -1, GETUTCDATE()))),
    @endDate = dbo.EndOfDay(COALESCE(@endDate, GETUTCDATE()))


;WITH cte1 AS
(
    SELECT  *
            , ROW_NUMBER() OVER 
                (PARTITION BY StaffIDAffected, CAST(RecordedOn AS DATE) ORDER BY RecordedOn) 
                AS num
                ,CASE WHEN ([Type] = 'AUS') THEN 'Sign In' WHEN ([Type] = 'AUSO') THEN 'Sign Out' END AS [Status]
    FROM    Logs
    WHERE [Type] IN ('AUS','AUSO')
    AND StaffIDAffected = @staffID
    AND RecordedOn BETWEEN @startDate AND @endDate
)

SELECT  l1.StaffIDAffected
        , l1.RecordedOn [SignIn]
        , l2.RecordedOn [SignOut]
FROM    cte1 l1
left outer JOIN   
        cte1 l2 ON 
        l2.StaffIDAffected = l1.StaffIDAffected
AND     CAST(l2.RecordedOn AS DATE) = CAST(l1.RecordedOn AS DATE)
AND     l2.num = l1.num + 1
WHERE   l1.status = 'Sign In'
    AND (l2.Status IS NULL OR l2.Status = 'Sign Out')

Please note that this app did not include the app as I was just trying to get it to get the correct values ​​with the name / id NULL ...

4 2015-06-01 14:05:02.900 2015-06-01 14:06:01.470 4 2015-06-01 14:56:33.107 2015-06-01 14:56:43.783 4 2015-06-01 15:06:33.403 2015-06-01 15:06:45.843 4 2015-06-01 16:00:35.477 2015-06-01 16:01:47.703 4 2015-06-01 16:02:20.487 2015-06-01 16:03:34.827 4 2015-06-01 16:09:14.353 2015-06-01 16:09:22.213 4 2015-06-01 16:13:26.377 2015-06-01 16:14:01.560

+3


source to share


1 answer


Since we don't have a violin, I can only suggest. Pivot will be grouped by columns from subquery that are not in aggregation

part and spreading

part ie Group will be grouped StaffName, ApplicationName, ApplicationID

. This way, you will end up with multiple rows where there are different combinations of these columns.

Now it stitches up that you want all lines login

with the nearest lines logout

. If so, you can do something like this:



select c1.ApplicationID, 
       c1.ApplicationName, 
       c1.StaffName,
       c1.RecordedOn as SignIn,
       oa.RecordedOn as SignOut
from cte1 c1
outer apply( select top 1 RecordedOn from cte2 c2 
             where c1.ApplicationID = c2.ApplicationID and 
                   c1.StaffName = c2.StaffName and 
                   c2.RecordedOn > c1.RecordedOn and
                   c2.InOut = 'Sign Out' and
                   c1.num + 1 = c2.num
             order by c2.RecordedOn) oa
where c1.InOut = 'Sign In'
+2


source






More articles:


All Articles