Most Frequent Logon, Asset Owner, Primary User

[sqldba01]

This can be used to get the most frequent logon account in the last 60 days. 

-- First query: Get the most frequent logon user for each AssetID

WITH LogonCounts

AS (SELECT AssetID,

username,

COUNT(*) AS logon_count,

ROW_NUMBER() OVER (PARTITION BY AssetID ORDER BY COUNT(*) DESC) AS rn

FROM tblCPlogoninfo

WHERE logontime >= DATEADD(DAY, -60, GETDATE())

GROUP BY AssetID,

username)

SELECT AssetID,

username AS PrimaryLogin,

logon_count

INTO #tempLogonInfo

FROM LogonCounts

WHERE rn = 1

ORDER BY AssetID;

[Mister_Nobody]

I use this code for assets report:

 Reverse(Stuff(Reverse((Select x.Username + ', ' From (Select cpli.Username,
          count(*) As name_count,
          Rank() Over (Order By count(*) Desc) As rank
        From tblCPlogoninfo cpli
        Where cpli.AssetID = tblAssets.AssetID
        Group By cpli.username) x
    Where x.rank = 1 For Xml Path(''))), 1, 2, '')) As MostFrequentUsers,

[Mister_Nobody]

LS on-perm:

With LogonCounts As (Select tblCPlogoninfo.AssetID,
      tblCPlogoninfo.username,
      COUNT(*) As logon_count,
      Row_Number() Over (Partition By tblCPlogoninfo.AssetID Order By
      COUNT(*) Desc) As rn
    From tblCPlogoninfo
    Where tblCPlogoninfo.logontime >= DateAdd(DAY, -60, GetDate())
    Group By tblCPlogoninfo.AssetID,
      tblCPlogoninfo.username)
Select LogonCounts.AssetID,
  LogonCounts.username As PrimaryLogin,
  LogonCounts.logon_count
From LogonCounts
Where LogonCounts.rn = 1

[DavidPK]

Hi sqldba01,

Thank you for your contributions to the community.