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

[neiko69]

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

Thanks

Nick

[Bruce_Garoutte]

This was exactly what I was looking for.
Thanks!!

[neiko69]

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?

[RCorbeil]

Give this a try:

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:

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