Notification

Icon
Error

Detailed list of machines that users from a specific OU have logged into for the last 90 days - Detailed list of machines that users from a specific OU have logged into for the last 90 days

Posted: Thursday, March 5, 2020 5:08:02 PM(UTC)
Nick Hogan

Nick Hogan

Member Original PosterPosts: 2
1
Like
Hi,
I'm trying to create a report that details all the machines that users from a specific OU (select admin accounts) have logged into over the last 90 days. I can list the users required from the OU but unsure how to then feed this list into the report i need.

Can anyone help? and sorry, i'm new to this product d'oh!

Thanks

Nick
RC62N
#1RC62N Member Posts: 577  
posted: 3/9/2020 7:21:41 PM(UTC)
Give this a try:
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join (SELECT
                tblCPlogoninfo.AssetID,
                Count(*) AS LogonCount
              FROM
                tblCPlogoninfo
                Inner Join tblADusers On tblCPlogoninfo.Domain = tblADusers.Userdomain And tblCPlogoninfo.Username = tblADusers.Username
              WHERE
                tblCPlogoninfo.logontime >= GetDate() - 90
                AND tblADUsers.OU LIKE '%your_ou_text%'
              GROUP BY
                tblCPlogoninfo.AssetID
             ) AS OULogons ON OULogons.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1

tblCPlogoninfo contains the historical logon events. Link that against tblADusers to get the user AD info.

The sub-SELECT should pull the historical list of logins that meet your 90-day & OU requirements. The INNER JOIN against it will limit the main SELECT to only those assets that appear in the sub-SELECT list.

The Count(*) in the sub-SELECT is optional; I used to for testing. If you don't care about it, you could as easily:
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join (SELECT Distinct
                tblCPlogoninfo.AssetID
              FROM
                tblCPlogoninfo
                Inner Join tblADusers On tblCPlogoninfo.Domain = tblADusers.Userdomain And tblCPlogoninfo.Username = tblADusers.Username
              WHERE
                tblCPlogoninfo.logontime >= GetDate() - 90
                AND tblADUsers.OU LIKE '%your_ou_text%'
             ) AS OULogons ON OULogons.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1
Nick Hogan
#2Nick Hogan Member Original PosterPosts: 2  
posted: 3/10/2020 12:25:29 PM(UTC)
WOW, that's just what i need, added a few bits;

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssets.Domain,
tblAssets.Description
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select tblCPlogoninfo.AssetID,
Count(*) As LogonCount
From tblCPlogoninfo
Inner Join tblADusers On tblCPlogoninfo.Domain = tblADusers.Userdomain
And tblCPlogoninfo.Username = tblADusers.Username
Where tblCPlogoninfo.logontime >= GetDate() - 90 And
tblADusers.OU Like
'OU=Admin Accounts,OU=ITServices,OU=ADUsers,OU=ALW,DC=xBridgewater,DC=nhs,DC=uk'
Group By tblCPlogoninfo.AssetID) As OULogons On OULogons.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1

How would you add the source machine name of the the person logging into the other machine?
Bruce Garoutte
#3Bruce Garoutte Member Posts: 5  
posted: 9/17/2021 8:23:35 PM(UTC)
This was exactly what I was looking for.
Thanks!!

Active Discussions

Lansweeper Chrome 95 Audit
by  Esben.D  
Go to last post Go to first unread
Last post: Yesterday at 11:14:31 AM(UTC)
Lansweeper Detecting and Reporting on Programs in Apps & Features
by  LANGuy  
Go to last post Go to first unread
Last post: 10/18/2021 6:09:11 PM(UTC)
Lansweeper OS: Not latest Build of Windows 10 not working
by  jmm25   Go to last post Go to first unread
Last post: 10/18/2021 12:48:41 PM(UTC)
Lansweeper AD DistinguishedName of Computer object
by  elKastr0nom   Go to last post Go to first unread
Last post: 10/14/2021 6:52:50 PM(UTC)
Lansweeper Report if Credential Guard is active
by  ericatbrandmauer  
Go to last post Go to first unread
Last post: 10/14/2021 10:28:15 AM(UTC)