AD Users not linked to any AssetID

[esr]

I've had a request to generate a list of AD Users who are not linked to any assets in Lansweeper. I've looked over tblUsers, tblADUsers and tblCPlogoninfo but, at first glance, they only seem to populate if there's an AssetID too.

So, does Lansweeper populate from the AD User scanning based on users found as it scans assets, or does it pull all Users from AD over into it's database? If so would you point me to the proper table to use?

So far I've both looked over tables and have run reports set to provide results based on users with AssetID fields that are like '' or is null but my reports don't return any results.


As an example, we've found a few user pages that don't list any assets- that is, no Last Computer is shown for them. We'd like to generate a list of these users for review.

Thanks!

[esr]

Sure enough, including [Userdomain] did the trick.

We only work with the one domain, so we often don't include that column. I'll be sure to include it as needed going forward. Thanks again for your assistance!!

[Susan_A]

You need to include tblADusers.Username and tblADusers.Userdomain in your report as well. If you include tblADusers.Username, tblADusers.Userdomain and tblADusers.Displayname in your report, without aliases, the Displayname column will be clickable within the report results.

Keep in mind as well that tblADusers must be linked to other tables on both the Username and Userdomain fields, as a username in itself is not unique. Your latest report will return incorrect results if you have multiple domains and the same username exists in more than one domain.

[esr]

Thanks for the tip on using tblADUsers.Displayname

I plugged it into the report shown earlier in this thread, and found the names were not presented as clickable links (no alias set as instructed, see screenshots & test SQL query below). As a test I took the core basic New Report and added in the Displayname as shown below, and no doubt I'm doing or overlooking something simple, but still the name does not appear as a clickable link to that users page.


Simple default report with tblADUsers.Displayname added in as a test:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblADusers.Displayname,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssetCustom.State = 1

[Jeremy_D]

You can add the Displayname field of the tblADusers table to your report. This column is clickable within the report results and links to the user pages. Make sure you don't assign an alias to the field though, or the links will be removed.

[esr]

Thank you for the table details! The historical data is helpful and a nice addition beyond the active when scanned data. By far the majority of our users live on a single PC, but the historical data is an extra layer to verify & help troubleshoot the symptoms.

In our case we're only monitoring a single domain (active domain scanning, and user group scanning is being used), so I've elected to not make that column visible. I've also added some extra detail for "at a glance" info like the Description field.

Question- is there a column available that will make a user name clickable to that user page, like AssetID is for asset pages? We're currently forcing a URL, but would love it to be in the name like with assets if that's possible!

Current code:

Select Distinct Top 1000000 tblADusers.Username,
  'User Page for: ' + tblADusers.Username As hyperlink_name_hyp,
  'http://<main web console URL>/user.aspx?username=' +
  Cast(tblADusers.Username As nvarchar) + '&userdomain=XXXXXXX' As hyperlink_hyp,
  tblADusers.Description,
  tblADusers.whenCreated,
  tblADusers.Lastchanged,
  tblADusers.OU
From tblADusers
  Left Join tblCPlogoninfo On tblCPlogoninfo.Username = tblADusers.Username And
    tblCPlogoninfo.Domain = tblADusers.Userdomain
Where (tblCPlogoninfo.AssetID Is Null) Or
  (tblCPlogoninfo.AssetID = '')
Order By tblADusers.Username

[Susan_A · Accepted solution]

TblUsers stores information on local users, tblADusers stores information and attributes of Active Directory users and tblCPlogoninfo stores logon events of both local and Active Directory users. When a Windows computer is scanned, Lansweeper automatically detects the currently logged on (local or AD) user, but additional AD users can be pulled directly from AD with Domain User Scanning as well, configured under Configuration\Scanning Methods. If an AD user page does not have computers listed, that means the user has not logged into a computer (yet) during a computer scan, which is when logon events get added to the database.

The report in your latest post won't be fully accurate, because tblADusers is incorrectly linked to tblAssets - it should be linked on the Userdomain field as well - and because tblAssets only stores the last logged on user of a computer. Your report lists users that are not the last logged on user of any computer. A user that is not the last logged on user of any computer can still have logon events listed on his user webpage, e.g. if he was the second to last user to log into a computer. To list AD users without logon events on their user webpages, you need to build a report based on tblCPlogoninfo instead. I've posted a report below.

Select Distinct Top 1000000 tblADusers.Username,
  tblADusers.Userdomain,
  tblADusers.OU
From tblADusers
  Left Join tblCPlogoninfo On tblCPlogoninfo.Username = tblADusers.Username And
    tblCPlogoninfo.Domain = tblADusers.Userdomain
Where (tblCPlogoninfo.AssetID Is Null) Or
  (tblCPlogoninfo.AssetID = '')
Order By tblADusers.Userdomain,
  tblADusers.Username

[esr]

Seems I was trying way, way too hard to make this happen- it's super easy actually!

Here's the core logic- I've, of course, added a ton of other details, but this is the really simple core that brings the data we wanted-

Select Top 1000000 tblADusers.Username,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblADusers.OU
From tblAssets
  Right Join tblADusers On tblADusers.Username = tblAssets.Username
Where (tblAssets.AssetID Like '') Or
  (tblAssets.AssetID Is Null)
Order By tblADusers.Username,
  tblADusers.OU

This effectively brings all the user names, then filters out those with an assetid associated. It appears to work perfectly for this particular goal, but I'd be happy for any improvements!