cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dsn7287
Engaged Sweeper II
Hi I would please like some help with the following report.

So I've got an issue with multiple domain users logging into a single workstation (true for more than just one workstation though across our domain). I'd like a report that gives me a clearer view of this - basically a list of workstations where more than just one user has logged into over the last 30 days. I found a report template for something similar on the community forum here, but it does not seem to provide a clear view of this and creates entries for every single login.

Would you be able to provide some assistance with this please?

The code I am using is below:


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblCPlogoninfo.logontime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblCPlogoninfo.logontime > GetDate() - 7 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblCPlogoninfo.logontime Desc
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I deleted your other forum topic because it appeared to be a duplicate of the followup question posted here. Below is a modified query that should do what you need.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery2.UserCount,
SubQuery3.[User]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select Top 1000000 SubQuery1.AssetID,
Count(SubQuery1.[User]) As UserCount
From (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery1
Group By SubQuery1.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Inner Join (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery3
On SubQuery3.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where SubQuery2.UserCount > 1 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
SubQuery3.[User]

View solution in original post

6 REPLIES 6
apurvs
Engaged Sweeper
We would like to have asset & it's all the user's login duration for the period of last 30 days. So that we can analyze and conclude the user with highest login duration as the owner of that asset. Is such kind of report possible?
Jeremy_D
Champion Sweeper
apurvs wrote:
We would like to have asset & it's all the user's login duration for the period of last 30 days. So that we can analyze and conclude the user with highest login duration as the owner of that asset. Is such kind of report possible?


Login duration is not currently scanned by Lansweeper, so this cannot be reported on, unfortunately. We have added this feature to our customer wish list but do not have a release date for this yet.
dsn7287
Engaged Sweeper II
Thanks so much Susan, this produced the EXACT report we were looking for! 🙂
Susan_A
Lansweeper Alumni
I deleted your other forum topic because it appeared to be a duplicate of the followup question posted here. Below is a modified query that should do what you need.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery2.UserCount,
SubQuery3.[User]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select Top 1000000 SubQuery1.AssetID,
Count(SubQuery1.[User]) As UserCount
From (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery1
Group By SubQuery1.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Inner Join (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery3
On SubQuery3.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where SubQuery2.UserCount > 1 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
SubQuery3.[User]
dsn7287
Engaged Sweeper II
Hi Susan,

Thank you so much for your timely reply!

This report is almost perfect, only I would like to also know which users are logging in to those machines. The count is great, but I would like to be able to identify each of these users and inform them that they are in violation of company policy.

Any assistance would be greatly appreciated.

Kind regards,
Dimitry
Susan_A
Lansweeper Alumni
I included a sample report below. Instructions for adding the report to your installation can be found here. 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 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery2.UserCount
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select Top 1000000 SubQuery1.AssetID,
Count(SubQuery1.[User]) As UserCount
From (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery1
Group By SubQuery1.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where SubQuery2.UserCount > 1 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName