Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-21-2016 08:34 AM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-22-2016 11:21 AM
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
1 REPLY 1
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-22-2016 11:21 AM
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