Show last user / logon time for all AD machines

[mmotti]

Hi,

I'm writing a Powershell script to update descriptions in AD for each machine.

Essentially I'm including details in the following format:

User Display Name [19/09/14 08:37:23] [HP ProBook 450 G2] [Windows 7 Professional (x64) SP1]

I took the query below from another post on this forum, but it seems to look at an individual's last logon location, so if a user logs on to multiple machines and I make a change to the format above, it will only be picked up for the machine that the user has most recently logged on to.

Select Top 1000000 SubQuery.Username,
                        SubQuery.Domain As Userdomain,
                        SubQuery.LastLogon,
                        tblAssets.AssetName as ComputerName,
                        tblAssetCustom.Manufacturer,
                        tblAssetCustom.Model,
                        tblAssets.Domain,
                        tblOperatingsystem.Caption as OperatingSystem,
                        tblComputersystem.SystemType,
                        tblOperatingsystem.ServicePackMajorVersion as ServicePack
                        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 tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
                        Inner Join tblOperatingSystem On tblAssets.AssetID = tblOperatingsystem.AssetID
                        Inner Join tblComputersystem on tblAssets.AssetID = tblComputersystem.AssetID

Essentially, I'd like for this report to output all of the same info. but instead focus the query on the last user for each asset, instead of it being the other way around!

Is anybody able to help? 🙂

Cheers,

Matt

[mydurian]

'last logon' should be [last logon].

[mmotti]

Exactly what I needed!

Thank

[Hemoco · Accepted solution]

Please use the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Userdomain As [last user domain],
tblAssets.Username As [last user],
Max(tblCPlogoninfo.logontime) As 'last logon'
From tblCPlogoninfo
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Userdomain,
tblAssets.Username

To use the report above, do the following:
•Open the report builder under Reports/Create New Report.
•Paste the SQL code we provided at the bottom of the page.
•Left-click somewhere in the upper section of the page so the code applies.
•Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.