List Last Logon User / Computer

[Scatt]

Hallo,

How i can generate a custom Report with the Username and the Last Logon Computer?


Scatt

[MikeMc]

The report pulls from the logoninfo table which makes no distinction of active users. I did tweak the original query to not include domain users in a disabled users OU. You can change the label on line 37 as needed.

Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.Image As icon,
Round((Cast((DateDiff(mm, tblBIOS.ReleaseDate, GetDate())) As float) / 12),
0) As [PC Age],
tsysOS.OSname As OS,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As [Disk size],
Cast(Cast(Cast(tblAssets.Memory As bigint) / 1024 As numeric) As nvarchar) As
RAM,
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssetCustom.Serialnumber As Serial,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.IPAddress As [IP Address],
tblAssets.AssetID
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Left Join tblADUsers ON tblADUsers.Userdomain = SubQuery.Domain AND tblADUsers.Username = SubQuery.Username
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1 
	And tblADUsers.OU Not Like '%Disabled Users%'
Order By Userdomain

[mrdaytrade]

As always, you guys are the best! Thank you for your help.

[Hemoco]

You should link to tblAssets the same way as in your current report. Try:

Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.Image As icon,
Round((Cast((DateDiff(mm, tblBIOS.ReleaseDate, GetDate())) As float) / 12),
0) As [PC Age],
tsysOS.OSname As OS,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As [Disk size],
Cast(Cast(Cast(tblAssets.Memory As bigint) / 1024 As numeric) As nvarchar) As
RAM,
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssetCustom.Serialnumber As Serial,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.IPAddress As [IP Address],
tblAssets.AssetID
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By Userdomain

[hasayeret]

Lansweeper wrote:
You should link to tblAssets the same way as in your current report. Try:

Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.Image As icon,
Round((Cast((DateDiff(mm, tblBIOS.ReleaseDate, GetDate())) As float) / 12),
0) As [PC Age],
tsysOS.OSname As OS,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As [Disk size],
Cast(Cast(Cast(tblAssets.Memory As bigint) / 1024 As numeric) As nvarchar) As
RAM,
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssetCustom.Serialnumber As Serial,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.IPAddress As [IP Address],
tblAssets.AssetID
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By Userdomain

Guys I used it and it's awesome! thanks so much!
By the way: why do I still see some users that have been disabled in my AD and moved to the disabled users OU?

thanks again!

[mrdaytrade]

I like the Basic report above, but I am having trouble adding other tables. My subquery knowledge is limited.

I would like to link the tables that provide the following information.
PC Name
PC Age using bios date
OSname
RAM
Nr of Processors
Manufacturer/Model
HD Size C: drive
Serial #


Here is the code that I made to gather the info I need. My report shows a list of assets with the last user that signed in. I would like the report to show the last pc a user signed into along with the info above. My code that needs altering. Your help is greatly appreciated.

Select Top 1000000 tblAssets.Username As Username,
tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.Image As icon,
Round((Cast((DateDiff(mm, tblBIOS.ReleaseDate, GetDate())) As float) / 12),
0) As [PC Age],
tsysOS.OSname As OS,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As [Disk size],
Cast(Cast(Cast(tblAssets.Memory As bigint) / 1024 As numeric) As nvarchar) As
RAM,
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssetCustom.Serialnumber As Serial,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.IPAddress As [IP Address]
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By OS Desc

[Hemoco · Accepted solution]

A basic report can be seen below.

Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetName,
tblAssets.Domain
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID

[stimpy_555]

Select top 100 percent AssetInfo.AssetName
,AssetInfo.SerialNumber
,CASE AssetInfo.ChassisTypes
when '3' then 'Desktop'
when '4' then 'Desktop'
when '6' then 'Desktop'
when '15' then 'Desktop'
when '7' then 'CAD'
when '8' then 'Laptop'
when '9' then 'Laptop'
when '10' then 'Laptop'
when '1' then 'Virtual'
when '23' then 'Server'
else 'Unknown'
end
AS DeviceType
,AssetInfo.Vendor
,AssetInfo.Model
,AssetInfo.OperatingSystem
,AssetInfo.OperatingSystemServicePack
,AssetInfo.WKS_AD_Location
,AssetInfo.IPAddress
,AssetInfo.ComputerOU
,AssetInfo.Lastseen
,AssetInfo.LastActiveScan
,AssetInfo.LastChanged
,LogonInfo.Username
,LogonInfo.Domain
From
(
SELECT DISTINCT
ASSETS.AssetName
,ASCUST.Serialnumber As SerialNumber
,ASCUST.Manufacturer as Vendor
,ASCUST.Model
,ASOP.Caption as OperatingSystem
,ASOP.ServicePackMajorVersion as OperatingSystemServicePack
,COMP.Location as WKS_AD_Location
,ASSETS.IPAddress As IPAddress
,COMP.OU AS ComputerOU
,ASSETS.Lastseen
,ASSETS.LastActiveScan
,COMP.LastChanged
,SYSEN.ChassisTypes
From dbo.tblAssets ASSETS
Left Join dbo.tblADComputers COMP on ASSETS.AssetID = COMP.AssetID
Left Join dbo.tblAssetCustom ASCUST On ASSETS.AssetID = ASCUST.AssetID
Left Join dbo.tblOperatingSystem ASOP On ASSETS.AssetID = ASOP.AssetID
Left Join dbo.tblSystemEnclosure SYSEN On ASSETS.AssetID = SYSEN.AssetID
Where ASSETS.AssetType = -1
) AS AssetInfo

Left Join
(
SELECT DISTINCT
dbo.tblAssets.AssetName As AssetName
,MAX(dbo.tblCPlogoninfo.logontime) As LastLogon
,dbo.tblCPlogoninfo.Domain
,dbo.tblCPlogoninfo.Username
FROM dbo.tblCPlogoninfo
Inner Join dbo.tblAssets on dbo.tblCPlogoninfo.AssetID = dbo.tblAssets.AssetID
Where dbo.tblCPlogoninfo.Username not like '%SVC%'
GROUP BY dbo.tblAssets.AssetName,dbo.tblCPlogoninfo.Domain,dbo.tblCPlogoninfo.Username
) AS LogonInfo on AssetInfo.Assetname = LogonInfo.Assetname

Order By AssetInfo.AssetName

-------------------------------------------------------------------------------------------------

The Last Left Join is what you are after, just update the report to contain the information you want to return above the Joins & From.. V5 Report..

[stimpy_555]

See my current posts.. This includes the logic to get the Last Logon with an exclude for my sevice accounts.