Report of multiple users accessing a single workstation

[dsn7287]

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

[apurvs]

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]

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]

Thanks so much Susan, this produced the EXACT report we were looking for! 🙂

[Susan_A · Accepted solution]

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]

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]

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