User logon history

[Darth_Zerpa]

Hi guys, I have the query below to get the logon history for each user, the problem is that the report is too large, is there a way to restrict on showing only the last 5 logins per user? With some users I'm getting up to 20 logins and we have more than 17k AD accounts. Thank you in advance.

Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.logontime,
tblAssets.AssetID,
tblAssets.AssetName
From tblAssets
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Order By tblCPlogoninfo.Username,
tblCPlogoninfo.logontime

[Darth_Zerpa]

No problem, thank you anyways AZHockeyNut...

[AZHockeyNut]

sorry about that, it works in sql but not in the report view for some reason. I am not that versed in sql to tell you why. Perhaps the folks in support could chime in. The query is valid in Management studio.

[AZHockeyNut]

well, depends what you mean by last 5 logins per user.
you could mean last 5 assets they logged into regardless of when, or you could mean the last 5 logontimes regardless of asset.

To get the last 5 logon times per user you would run something like this....

WITH TopFive AS
(
SELECT l.UserName, l.LogOnTime, a.AssetId, a.AssetName, ROW_NUMBER() OVER (PARTITION BY l.UserName ORDER BY l.LogOnTime DESC) AS RowNum
FROM tblAssets AS a
INNER JOIN tblCPLogOnInfo AS l ON a.AssetId = l.AssetId
)
SELECT UserName, LogOnTime, AssetId, AssetName
FROM TopFive
WHERE RowNum <= 5
ORDER BY UserName ASC, LogOnTime DESC

[Darth_Zerpa]

AZHockeyNut wrote:
well, depends what you mean by last 5 logins per user.
you could mean last 5 assets they logged into regardless of when, or you could mean the last 5 logontimes regardless of asset.

To get the last 5 logon times per user you would run something like this....

WITH TopFive AS
(
SELECT l.UserName, l.LogOnTime, a.AssetId, a.AssetName, ROW_NUMBER() OVER (PARTITION BY l.UserName ORDER BY l.LogOnTime DESC) AS RowNum
FROM tblAssets AS a
INNER JOIN tblCPLogOnInfo AS l ON a.AssetId = l.AssetId
)
SELECT UserName, LogOnTime, AssetId, AssetName
FROM TopFive
WHERE RowNum <= 5
ORDER BY UserName ASC, LogOnTime DESC

Hi, thank you for taking the time one reading this, I would like to get the last 5 logins per user regardless of the computer (can be 5 times the same computer if the user only uses 1 computer or 5 different computers if the user normally logs in everywhere). Basically we want to identify users that are using computers that they shouldn't.

By the way, I tried your query but gives me the following error:

Error while saving report: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expression, unless TOP, OFFSET or FOR XML is also specified."