Report to determine users most frequently used PC

[ruggj]

Hi all,

I'm looking for a report that can determine which PC a user most frequently logs on to and what department they are from based on AD.
We are trying to determine the PC name of each users PC. Unfortunately as staff may move around to a different desk for a day we can't just rely on last log on time, however if we can count the amount of times they have logged on to a PC we can safely assume that whichever PC they have logged on to the most is the main PC they use.

I am new to Lansweeper and don't have much SQL experience so I am not sure how I would do this, any help would be appreciated.

Thanks

[Susan_A · Accepted solution]

I included a sample report below that lists your domain users and their department. For each user, it also lists the computer the user has the most logon events for. 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.

Select Top 1000000 SubQuery2.Username,
  SubQuery2.Userdomain,
  tblADusers.Department,
  SubQuery2.LogonCount,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.AssetID,
  tblAssets.IPAddress
From (Select Top 1000000 SubQuery.Username,
    SubQuery.Userdomain,
    Max(SubQuery.Count) As LogonCount
  From (Select Top 1000000 tblCPlogoninfo.Username,
      tblCPlogoninfo.Domain As Userdomain,
      tblCPlogoninfo.AssetID As ID,
      Count(tblCPlogoninfo.ID) As Count
    From tblCPlogoninfo
    Group By tblCPlogoninfo.Username,
      tblCPlogoninfo.Domain,
      tblCPlogoninfo.AssetID) SubQuery
  Group By SubQuery.Username,
    SubQuery.Userdomain) SubQuery2
  Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain As Userdomain,
    tblCPlogoninfo.AssetID As ID,
    Count(tblCPlogoninfo.ID) As Count
  From tblCPlogoninfo
  Group By tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain,
    tblCPlogoninfo.AssetID) SubQuery3 On SubQuery3.Username = SubQuery2.Username
    And SubQuery3.Userdomain = SubQuery2.Userdomain And SubQuery3.Count =
    SubQuery2.LogonCount
  Inner Join tblAssets On tblAssets.AssetID = SubQuery3.ID
  Inner Join tblADusers On tblADusers.Username = SubQuery2.Username And
    tblADusers.Userdomain = SubQuery2.Userdomain
Order By SubQuery2.Userdomain,
  SubQuery2.Username