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
0
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: 434  
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?

Active Discussions

Lansweeper Unable to send to External Email
by  pryan67   Go to last post Go to first unread
Last post: Yesterday at 8:21:01 PM(UTC)
Lansweeper Lansweeper assets not being Scanned
by  Jordan  
Go to last post Go to first unread
Last post: Yesterday at 6:42:22 PM(UTC)
Lansweeper Database size growing too large
by  bladd   Go to last post Go to first unread
Last post: Yesterday at 4:26:44 PM(UTC)
Lansweeper Searching Specific File
by  mzipperer  
Go to last post Go to first unread
Last post: Yesterday at 4:23:29 PM(UTC)
Lansweeper Exchange 2010 information is not populating
by  Moe   Go to last post Go to first unread
Last post: Yesterday at 12:30:38 PM(UTC)
Lansweeper Routinely Exploited Vulnerabilities Query Report
by  pryan67  
Go to last post Go to first unread
Last post: Yesterday at 12:25:31 PM(UTC)
Lansweeper Creating a report for new devices every 2 hours to alert
by  Moe   Go to last post Go to first unread
Last post: 6/4/2020 7:26:11 PM(UTC)
Lansweeper Changes in the licensing model
by  BullGates  
Go to last post Go to first unread
Last post: 6/4/2020 6:27:23 PM(UTC)