Hello. I have half year IIS log.
I need count last user activity by dates from first day activity.
Today count:
SELECT COUNT()
FROM
(SELECT
MAX(DATEADD(HOUR,3,uac.dt)) as [LastLoginTime],
spuinf.tp_Title,
spuinf.tp_Login,
convert (int, psuinf.ResourceIsActive) as ResourceIsActive
from
[PS_PROD].[dbo].[User_Activity] as uac
inner join [WSS_PWA].[pjrep].[MSP_EpmResource_UserView] as psuinf on LOWER(psuinf.[ResourceUID])=LOWER(uac.[ProjectUserGUID])
inner join [WSS_PWA].[dbo].[UserInfo] as spuinf on LOWER(spuinf.[tp_Login])=LOWER(psuinf.[ResourceNTAccount])
inner join [PS_PROD].[dbo].[ClientsENUM] as clie on clie.[SearchStr]=uac.[csUserAgent]
inner join [PS_PROD].[dbo].[Source] as src on src.[ID]=clie.[SourceID]
inner join [PS_PROD].[dbo].[LinksENUM] as lkse on lkse.[SearchStr]=uac.[csUriStem]
inner join [PS_PROD].[dbo].[Destination] as dst on dst.[ID]=lkse.[DestinationID]
group by
spuinf.tp_Title,
spuinf.tp_Login,
convert (int, psuinf.ResourceIsActive)
HAVING
MAX(DATEADD(HOUR,3,uac.dt)) <= GETDATE()) as T1
Yesterday count
SELECT COUNT()
FROM
(SELECT
MAX(DATEADD(HOUR,3,uac.dt)) as [LastLoginTime],
spuinf.tp_Title,
spuinf.tp_Login,
convert (int, psuinf.ResourceIsActive) as ResourceIsActive
from
[PS_PROD].[dbo].[User_Activity] as uac
inner join [WSS_PWA].[pjrep].[MSP_EpmResource_UserView] as psuinf on LOWER(psuinf.[ResourceUID])=LOWER(uac.[ProjectUserGUID])
inner join [WSS_PWA].[dbo].[UserInfo] as spuinf on LOWER(spuinf.[tp_Login])=LOWER(psuinf.[ResourceNTAccount])
inner join [PS_PROD].[dbo].[ClientsENUM] as clie on clie.[SearchStr]=uac.[csUserAgent]
inner join [PS_PROD].[dbo].[Source] as src on src.[ID]=clie.[SourceID]
inner join [PS_PROD].[dbo].[LinksENUM] as lkse on lkse.[SearchStr]=uac.[csUriStem]
inner join [PS_PROD].[dbo].[Destination] as dst on dst.[ID]=lkse.[DestinationID]
group by
spuinf.tp_Title,
spuinf.tp_Login,
convert (int, psuinf.ResourceIsActive)
HAVING
MAX(DATEADD(HOUR,3,uac.dt)) <= GETDATE()) as T1
Yesterday count:
SELECT COUNT(*)
FROM
(SELECT
MAX(DATEADD(HOUR,3,uac.dt)) as [LastLoginTime],
spuinf.tp_Title,
spuinf.tp_Login,
convert (int, psuinf.ResourceIsActive) as ResourceIsActive
from
[PS_PROD].[dbo].[User_Activity] as uac
inner join [WSS_PWA].[pjrep].[MSP_EpmResource_UserView] as psuinf on LOWER(psuinf.[ResourceUID])=LOWER(uac.[ProjectUserGUID])
inner join [WSS_PWA].[dbo].[UserInfo] as spuinf on LOWER(spuinf.[tp_Login])=LOWER(psuinf.[ResourceNTAccount])
inner join [PS_PROD].[dbo].[ClientsENUM] as clie on clie.[SearchStr]=uac.[csUserAgent]
inner join [PS_PROD].[dbo].[Source] as src on src.[ID]=clie.[SourceID]
inner join [PS_PROD].[dbo].[LinksENUM] as lkse on lkse.[SearchStr]=uac.[csUriStem]
inner join [PS_PROD].[dbo].[Destination] as dst on dst.[ID]=lkse.[DestinationID]
group by
spuinf.tp_Title,
spuinf.tp_Login,
convert (int, psuinf.ResourceIsActive)
HAVING
MAX(DATEADD(HOUR,3,uac.dt)) <= DATEADD(DAY,-1,GETDATE())) as T2
And so on till first date of time period.
Result:
2021.06.23 174
2021.06.22 164
2021.06.21 153
...
2021.01.01 0