Local Admin logged on to workstations report from last 24 hours

[Amador]

Needing a report that shows workstations has a local admin logged on from last 24 hours

I appreciate it and thanks in advance

[Joshua]

This is a pretty basic report that will show if any built in local admin accounts have logged into a computer in the past day. You can change DATEADD(day, -1, GETDATE()) to go back however many days you want.

SELECT tblCPlogoninfo.AssetID, tblAssets.AssetName, tblCPlogoninfo.LogonTime, tblCPlogoninfo.Username, tblCPlogoninfo.IPaddress
FROM tblCPlogoninfo
LEFT JOIN tblAssets ON tblCPlogoninfo.AssetID = tblAssets.AssetID
LEFT JOIN tblUsers ON tblUsers.AssetID = tblAssets.AssetID AND tblUsers.buildinadmin = 1
WHERE tblCPlogoninfo.Username = tblUsers.Name and tblCPlogoninfo.logontime >= DATEADD(day, -1, GETDATE())


If you want to get more complicated you can do something like the below that will show if anyone in the local administrator group logs into a machine. This report will get noisy if you have some people who are a local admin as it will show every time they login to their workstation.

SELECT tblCPlogoninfo.AssetID, tblAssets.AssetName, tblCPlogoninfo.LogonTime, tblCPlogoninfo.Username, tblCPlogoninfo.IPaddress
FROM tblCPlogoninfo
LEFT JOIN tblAssets ON tblCPlogoninfo.AssetID = tblAssets.AssetID
LEFT JOIN tblUsersInGroup ON tblUsersInGroup.AssetID = tblAssets.AssetID AND tblUsersInGroup.Groupname like 'Administrators' AND tblUsersInGroup.Username = tblCPlogoninfo.Username
WHERE tblUsersInGroup.Username IS NOT NULL and tblCPlogoninfo.logontime >= DATEADD(day, -1, GETDATE())

If you wanted to get even more complicated you could tie into AD groups that have local admin rights but I think that might be more than what you're asking.

[Amador]

Joshua wrote:
This is a pretty basic report that will show if any built in local admin accounts have logged into a computer in the past day. You can change DATEADD(day, -1, GETDATE()) to go back however many days you want.

SELECT tblCPlogoninfo.AssetID, tblAssets.AssetName, tblCPlogoninfo.LogonTime, tblCPlogoninfo.Username, tblCPlogoninfo.IPaddress
FROM tblCPlogoninfo
LEFT JOIN tblAssets ON tblCPlogoninfo.AssetID = tblAssets.AssetID
LEFT JOIN tblUsers ON tblUsers.AssetID = tblAssets.AssetID AND tblUsers.buildinadmin = 1
WHERE tblCPlogoninfo.Username = tblUsers.Name and tblCPlogoninfo.logontime >= DATEADD(day, -1, GETDATE())


If you want to get more complicated you can do something like the below that will show if anyone in the local administrator group logs into a machine. This report will get noisy if you have some people who are a local admin as it will show every time they login to their workstation.

SELECT tblCPlogoninfo.AssetID, tblAssets.AssetName, tblCPlogoninfo.LogonTime, tblCPlogoninfo.Username, tblCPlogoninfo.IPaddress
FROM tblCPlogoninfo
LEFT JOIN tblAssets ON tblCPlogoninfo.AssetID = tblAssets.AssetID
LEFT JOIN tblUsersInGroup ON tblUsersInGroup.AssetID = tblAssets.AssetID AND tblUsersInGroup.Groupname like 'Administrators' AND tblUsersInGroup.Username = tblCPlogoninfo.Username
WHERE tblUsersInGroup.Username IS NOT NULL and tblCPlogoninfo.logontime >= DATEADD(day, -1, GETDATE())

If you wanted to get even more complicated you could tie into AD groups that have local admin rights but I think that might be more than what you're asking.

Thank you very much Joshua this has helped me a ton! More power to you sir