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 Pro Tips #7: New Software Discovered!
by  Esben.D   Go to last post Go to first unread
Last post: 10/8/2021 10:55:47 AM(UTC)
Lansweeper Pro Tips #6: IP Address Management
by  Esben.D  
Go to last post Go to first unread
Last post: 10/6/2021 9:35:50 AM(UTC)
Lansweeper Pro Tips #5: Power Management
by  Esben.D   Go to last post Go to first unread
Last post: 9/10/2021 1:22:24 PM(UTC)
Lansweeper Pro Tips #4: Switching Up Your Dashboard
by  Esben.D  
Go to last post Go to first unread
Last post: 8/20/2021 12:34:27 PM(UTC)
Lansweeper Pro Tips #3: Discovering Remote Desktop Services
by  Esben.D   Go to last post Go to first unread
Last post: 8/20/2021 12:32:21 PM(UTC)
Lansweeper Happy SysAdmin Day!
by  Esben.D  
Go to last post Go to first unread
Last post: 7/30/2021 9:21:35 AM(UTC)
Lansweeper Pro Tips #2: Advanced Alerts
by  Esben.D   Go to last post Go to first unread
Last post: 7/26/2021 3:08:37 PM(UTC)
Lansweeper SysAdmin Day Awards 2021 – Vote for the Heroes of IT
by  Esben.D  
Go to last post Go to first unread
Last post: 7/19/2021 3:23:10 PM(UTC)