Notification

Icon
Error

Report Login time reduces number of computers by 300

Posted: Friday, July 23, 2021 4:21:33 PM(UTC)
cross_eur

cross_eur

Member Original PosterPosts: 11
0
Like
Hello, I am trying to understand why when i create a simple report to show user last login time, without the table tblCPlogoninfo linked, it shows 2000 computers, but as soon as I add that table it shows 300 computers less. Any help would be appreciated.


Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Lastseen As [LastSeen Ordinateur]
From tblAssets
Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =
tblAssetCustom.AssetID
Inner Join lansweeperdb.dbo.tblCPlogoninfo On tblAssets.AssetID =
tblCPlogoninfo.AssetID
Where tblAssets.AssetName Like 'z%'
RC62N
#1RC62N Member Posts: 575  
posted: 7/23/2021 4:55:13 PM(UTC)
First, from the query you presented, without the JOIN to tblCPlogoninfo you're listing all of your assets: Windows computers, printers, monitors, switches, etc. According to the database documentation:
Quote:
tblCPlogoninfo
This table stores the user logon events that occurred on your Windows computers.

When you INNER JOIN to tblCPlogoninfo, you're eliminating all assets that aren't Windows computers. That's one of the characteristics of an INNER join.

Additionally, if there are any Windows computers without anything recorded in tblCPlogoninfo, those assets will also be eliminated from the result set.

If all you want is a list of Windows computers, drop the JOIN to tblCPlogoninfo and add a filter to your WHERE clause:
Code:
Select Distinct Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Lastseen As [LastSeen Ordinateur]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
  tblAssets.AssetType = -1
  And tblAssets.AssetName Like 'z%'

If you'd prefer to filter the asset type by name than by code, link in the descriptions:
Code:
Select Distinct Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Lastseen As [LastSeen Ordinateur]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.AssetType
Where
  tsysAssetTypes.AssetTypename = 'Windows'
  And tblAssets.AssetName Like 'z%'

cross_eur
#2cross_eur Member Original PosterPosts: 11  
posted: 7/23/2021 6:05:42 PM(UTC)
Thank you for the Answer, it seems that i have 300 entries that are not corresponding to usernames in tblCPlogoninfo that have connected to a computer. I will look into that. I appreciate you taking the time to answer.



Originally Posted by: RC62N Go to Quoted Post
First, from the query you presented, without the JOIN to tblCPlogoninfo you're listing all of your assets: Windows computers, printers, monitors, switches, etc. According to the database documentation:
Quote:
tblCPlogoninfo
This table stores the user logon events that occurred on your Windows computers.

When you INNER JOIN to tblCPlogoninfo, you're eliminating all assets that aren't Windows computers. That's one of the characteristics of an INNER join.

Additionally, if there are any Windows computers without anything recorded in tblCPlogoninfo, those assets will also be eliminated from the result set.

If all you want is a list of Windows computers, drop the JOIN to tblCPlogoninfo and add a filter to your WHERE clause:
Code:
Select Distinct Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Lastseen As [LastSeen Ordinateur]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
  tblAssets.AssetType = -1
  And tblAssets.AssetName Like 'z%'

If you'd prefer to filter the asset type by name than by code, link in the descriptions:
Code:
Select Distinct Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Lastseen As [LastSeen Ordinateur]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.AssetType
Where
  tsysAssetTypes.AssetTypename = 'Windows'
  And tblAssets.AssetName Like 'z%'



Active Discussions

Lansweeper Scan User Exclusion / Define User OU for active scanning
by  Almada   Go to last post Go to first unread
Last post: Yesterday at 6:43:34 PM(UTC)
Lansweeper sqlServerId is changing
by  Ciro Bizelli  
Go to last post Go to first unread
Last post: Yesterday at 3:40:34 PM(UTC)
Lansweeper Slow Loading Lansweeper
by  Maikel Vanroelen   Go to last post Go to first unread
Last post: Yesterday at 9:25:31 AM(UTC)
Lansweeper Sync information
by  Jay-IT  
Go to last post Go to first unread
Last post: 9/16/2021 9:20:11 PM(UTC)
Lansweeper "Unknown" exclusion does not exclude Unknown assets
by  Almada   Go to last post Go to first unread
Last post: 9/16/2021 7:18:43 PM(UTC)
Lansweeper multiple scanning servers with granular permission
by  FrankSc  
Go to last post Go to first unread
Last post: 9/16/2021 6:29:54 PM(UTC)
Lansweeper Separate helpdesk websites?
by  JCochran   Go to last post Go to first unread
Last post: 9/15/2021 2:51:03 PM(UTC)
Lansweeper Remove Ads
by  FrankSc  
Go to last post Go to first unread
Last post: 9/15/2021 12:30:18 PM(UTC)