Last User Report List

[solutioncenter]

Hey all Anyone know how I can tweak this to only show me the distinct PC's on the domain, the way I have it too many results come out, basically I want a custom report that shows

"Username - PC - LastLogon Time" on all PC's in the Domain, so I can see PC's that haven't been logged on in a bit and who was the last person that did

This is what I got but it gives me too many results


SELECT TOP (100) PERCENT dbo.tblcomputers.ComputerUnique, dbo.tblcomputers.Username, dbo.tblcomputers.Computername, dbo.tblCPlogoninfo.logontime
FROM dbo.tblcomputers INNER JOIN
dbo.tblCPlogoninfo ON dbo.tblcomputers.Computername = dbo.tblCPlogoninfo.Computername
ORDER BY dbo.tblcomputers.ComputerUnique

thx!

[solutioncenter]

Most Excellent thx!, If I havent said it before LS and you Rock!

[Hemoco]

Select Top (100) Percent dbo.tblcomputers.ComputerUnique,
dbo.tblcomputers.Username, dbo.tblcomputers.Computername,
Max(dbo.tblCPlogoninfo.logontime)
From dbo.tblcomputers Left Join
dbo.tblCPlogoninfo On dbo.tblcomputers.Computername =
dbo.tblCPlogoninfo.Computername
Group By dbo.tblcomputers.ComputerUnique, dbo.tblcomputers.Username,
dbo.tblcomputers.Computername
Order By dbo.tblcomputers.ComputerUnique

[solutioncenter]

Looks good, I have a discrepancy between the PC's in this report 505 and the amount of PC's in tblComputers 565, I'm checking it out, but i would think it be nulls in the tblpclogoninfo. wonder if there's a way to parse the nulls as well to see machines that dont have the logontime value?

[Hemoco]

try this

Select Top (100) Percent dbo.tblcomputers.ComputerUnique,
dbo.tblcomputers.Username, dbo.tblcomputers.Computername,
Max(dbo.tblCPlogoninfo.logontime)
From dbo.tblcomputers Inner Join
dbo.tblCPlogoninfo On dbo.tblcomputers.Computername =
dbo.tblCPlogoninfo.Computername
Group By dbo.tblcomputers.ComputerUnique, dbo.tblcomputers.Username,
dbo.tblcomputers.Computername
Order By dbo.tblcomputers.ComputerUnique