Report on User Logins, Multiple Assets

[mhammond]

Ideally we are trying to eliminate the users from using 2 machines - like for remote work, a desktop AND a laptop, by just having the laptop; OR when a remote user gets an upgrade, ensure that they have sent back the old laptop instead of just using it AND the new laptop in tandem. In those cases, it is hardware that can be recycled and redeployed, as we are working on moving off of Windows 7 on to Windows 10.

I would like to generate a report that will parse out every machine that LS has detected them logging in to. If possible, since we use a lot of RemoteApps, I would like to filter out 'server' logins through remote desktop sessions.

However, like the brief blurb above reads, we have users that refuse to give us back their old hardware or at the very least, refuse to acknowledge they still have it.

We also would use the report to audit against future purchases, in the case of purchasing a whole new machine or just a slew of MAK Windows 10 licenses to deploy on hardware that can handle the OS.

I would imagine that the report would be based off of Last User Login and tsys.OS, etc. But I'm not sure how to write that out, nor am I able to find a built-in report I could customize for this type of request.

Does anyone have an idea of where I should begin the code?

[Andy_Sismey]

Sorry my fault I missed of the "Asset.ID" which is required to make the Asset Clickable !

Select Top 1000000 tblAssets.AssetName,
  Query1.Username,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.AssetID
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Right Join (Select Count(tblAssets.AssetID) As Count,
        tblAssets.Username
      From tblAssets
      Group By tblAssets.Username
      Having Count(tblAssets.AssetID) > 1) Query1 On Query1.Username =
    tblAssets.Username
Where tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  Query1.Username,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.AssetID,
  tblAssets.Username
Order By Query1.Username

[mhammond]

This is EXCELLENT!

I've used this basic code, then filtered out 'Admin%' username, and all the "universal" logins for 'dummy' terminals that only run simple programs on our shopfloor (where specific user names are not required for use of those machines).

Is there a way to make the Asset Names 'active'? As in, clickable from the report so that I can jump instantly to that asset on the list? It's not too big of a deal, since the 'meat and potatoes' of what I need is already here.

Thank you very MUCH for the help!

I can add more columns as my IT Director sees fit ... very easy report to use!

[Andy_Sismey]

Hi,

This may give you a starting point, so the report below is based on "Last Logged On" and lists all users / assets where the User Name for "Last logged On" > 1 , so they may have 2 assets or more, you may want to add a filter and remove Administrators, etc ?

Select Top 1000000 tblAssets.AssetName,
  Query1.Username,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Right Join (Select Count(tblAssets.AssetID) As Count,
        tblAssets.Username
      From tblAssets
      Group By tblAssets.Username
      Having Count(tblAssets.AssetID) > 1) Query1 On Query1.Username =
    tblAssets.Username
Where tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  Query1.Username,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Username,
  tblAssets.AssetID
Order By Query1.Username