User and PC Names Report

[HammettMike]

I have the following report:

Select Top 1000000 SubQuery.Username,
  SubQuery.Domain As Userdomain,
  SubQuery.LastLogon,
  tblAssets.AssetName,
  tblADusers.Displayname
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 tblADusers On tblADusers.Username = SubQuery.Username

The report is excluding PCs that weren't online the last time they were scanned. This is troublesome when users have laptops that aren't always on the network or when scans happen at 3 AM. The thing is, I don't see anything restricting the results.

[Susan_A · Accepted solution]

The specified report was written from a user perspective, not a computer perspective. It lists your users and the last computer each user logged into. If a computer was not the last logged on computer of a user, it won't be in the report results. The report is limited to domain users as well, due to the way tblADusers is linked. By default, SQL only displays a line in the report output if there is a record for both sides of the table relation, e.g. if there is a record for the user in both SubQuery and tblADusers. Only domain users have a record in tblADusers however, so only domain users will be listed, unless you change the table link. TblADusers is incorrectly linked in any case, as it should be linked on both the username and user domain fields. A username in itself is not unique.

If you are trying to list your computers and their last logged on user, simply open the report builder under Reports\Create New Report, tick the Username and Userdomain fields in tblAssets, give your report a title and hit Save & Run. To link tblADusers as well, follow these instructions.

If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.