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

Action Find lost space the easy way (spacesniffer.exe)
by  krozar   Go to last post Go to first unread
Last post: 5/5/2020 10:27:33 PM(UTC)
Lansweeper Querying a web server or web services
by  mmo  
Go to last post Go to first unread
Last post: 5/5/2020 9:00:36 PM(UTC)
Action Password Status
by  brodiemac-too   Go to last post Go to first unread
Last post: 4/23/2020 9:01:03 PM(UTC)
Action Email user
by  Michael Kop  
Go to last post Go to first unread
Last post: 4/7/2020 11:24:12 AM(UTC)
Lansweeper actualizacion de datos importando csv
by  jhon freddy   Go to last post Go to first unread
Last post: 3/31/2020 9:04:45 PM(UTC)
Lansweeper Asset Actions - RunAs Prompt
by  Socal_s197  
Go to last post Go to first unread
Last post: 3/26/2020 10:02:16 PM(UTC)
Lansweeper Remote Registry 2019
by  CyberCitizen   Go to last post Go to first unread
Last post: 3/6/2020 12:25:39 AM(UTC)
Lansweeper Installation Parameters
by  Florian_Eigsi  
Go to last post Go to first unread
Last post: 3/3/2020 10:17:18 AM(UTC)