Developer forum

Forum » Development » best database tables to get information about logins and visited paths by user?

best database tables to get information about logins and visited paths by user?

E-mail notifications
Pedro Meias
Pedro Meias
Reply
Posted on 02/08/2022 18:28:47

Hello,

we are trying to build 2 reports with the following outputs:

  • All login instances
  • All visited paths by user

can someone please advise what are the best database tables to look to this information?

 

Thank you.


Replies

 
Nicolai Pedersen
Posted on 03/08/2022 13:44:41
Reply
This post has been marked as an answer

Logins can be found in GeneralLog:

SELECT TOP (10) [LogID]
      ,[LogAction]
      ,[LogDescription]
      ,[LogUsername]
      ,[LogDate]
      ,[LogFilePath]
      ,[LogLevel]
      ,[UserId]
      ,[UtcOffset]
      ,[Exception]
      ,[MachineName]
      ,[Category]
      ,[Url]
  FROM [dw9].[dbo].[GeneralLog]
  where LogAction = 'Authentication' and UserId = 1

Their visits can be found in either Statv2Session or in TrackingSession and TrackingView tables depending on if you use the old or new statistics.

Also you can access these information on the user in the backend:

Votes for this answer: 1
 
Pedro Meias
Pedro Meias
Posted on 08/08/2022 14:52:31
Reply

Hi Nicolai,

Thank you for your help

 
Pedro Meias
Pedro Meias
Posted on 09/08/2022 02:49:53
Reply

Posting the queries for future reference

Login timestamps per login

SELECT  a.AccessUserUserName as UserName,
a.AccessUserName as Name,
a.AccessUserEmail as Email,
a.AccessUserAddress as Address,
a.AccessUserCity as City,
a.AccessUserState as State,
a.AccessUserCountry as Country,
a.AccessUserCustomerNumber as [Customer #], 
a.AccessUserExternalId as [User External Id],
a.AccessUser_ForceImpersonation as [Force Impersonation],
a.AccessUser_RememberUser as [Remember User],
a.AccessUser_LastImpersonatedUserId as [Last Impersonated UserId],
CASE WHEN MAX(b.Statv2SessionTimestampEnd) IS NULL THEN '' ELSE CONVERT(VARCHAR(10),MAX(b.Statv2SessionTimestampEnd),1) END as [Last Login]
FROM AccessUser as a
LEFT JOIN Statv2Session as b ON a.AccessUserId = b.Statv2SessionExtranetUserId
WHERE a.AccessUserUserName IS NOT NULL AND a.AccessUser_UserType = 'Login'
GROUP BY a.AccessUserName,
a.AccessUserUserName,
a.AccessUserEmail, 
a.AccessUserAddress,
a.AccessUserCity,
a.AccessUserState,
a.AccessUserCountry,
a.AccessUserCustomerNumber, 
a.AccessUserExternalId,
a.AccessUser_ForceImpersonation,
a.AccessUser_RememberUser,
a.AccessUser_LastImpersonatedUserId
 
Visited pages by Login
SELECT  a.AccessUserUserName as UserName,
a.AccessUserName as Name,
a.AccessUserEmail as Email,
a.AccessUserCustomerNumber as [Customer #], 
a.AccessUserExternalId as [User External Id],
e.Statv2ObjectTimestamp as [Timestamp],
b.Statv2SessionGwIP as [IP Address],
b.Statv2SessionUserAgentName as [Browser],
b.Statv2SessionUserAgentPlatform as [OS],
c.Statv2PagePageId as [PageId],
d.PageMenuText as [Page Name]
FROM AccessUser as a
LEFT JOIN Statv2Session as b ON a.AccessUserId = b.Statv2SessionExtranetUserId
LEFT JOIN Statv2Page as c on b.Statv2SessionId = c.Statv2PageSessionId
LEFT JOIN Page as d on c.Statv2PagePageId = d.PageId
LEFT JOIN Statv2Object as e ON (c.Statv2PagePageId = e.Statv2ObjectPageId AND b.Statv2SessionSessionId = e.Statv2ObjectSessionId)
WHERE a.AccessUserUserName IS NOT NULL AND a.AccessUser_UserType = 'Login' AND c.Statv2PagePageId <> -1
 
Nicolai Pedersen
Posted on 09/08/2022 16:29:01
Reply

Thanks for sharing!

 

You must be logged in to post in the forum