question

ZaytsevVasily-8124 avatar image
0 Votes"
ZaytsevVasily-8124 asked ZaytsevVasily-8124 commented

Request Last User Activity by dates in time period

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



sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

First of all, clean up our post, by adding the code samples with the insert-code button, that's the button with ones and zeroes on.

Next., you need to clarify what you are asking for. You seem to be posting three decently complex queries involving a number of tables that we don't know. What are these queries producing?

Finally, what is an IIS log? (I would think of the web server, but I would expect that to be a text file, not a couple of tables in a database.)

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ZaytsevVasily-8124 commented

Hi @ZaytsevVasily-8124

If you want to calculate the activities of each day in turn, please refer to the following code:

 DECLARE @Counter INT 
 SET @Counter=0
 WHILE ( @Counter <= DATEDIFF(dd,'2021-5-24',GETDATE()))
 BEGIN
     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,-@Counter,GETDATE()) as T1    
     SET @Counter  = @Counter  + 1
 END

Due to the lack of test code, the above method has not been tested. If your problem still cannot be solved,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …). So that we’ll get a right direction and make some test.


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello, Echo
Thank you very much and have a good day.
BR, Vasily

0 Votes 0 ·