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: 577  
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 Upgrade Win 10 build to version 2004
by  CyberCitizen   Go to last post Go to first unread
Last post: Today at 12:11:55 AM(UTC)
Lansweeper Sort Reports by Last Changed
by  LANGuy  
Go to last post Go to first unread
Last post: Yesterday at 7:02:23 PM(UTC)
Lansweeper Windows Encryption Method
by  elKastr0nom   Go to last post Go to first unread
Last post: Yesterday at 6:02:07 PM(UTC)
Lansweeper Built in report - AD Password Expired
by  FezUSA  
Go to last post Go to first unread
Last post: Yesterday at 3:12:17 PM(UTC)
Lansweeper Helpdesk Dashboard Shared Tab
by  mark chamberlain   Go to last post Go to first unread
Last post: Yesterday at 2:32:20 PM(UTC)
Lansweeper Uninstall an software with Password
by  CyberCitizen   Go to last post Go to first unread
Last post: Yesterday at 6:20:28 AM(UTC)
Lansweeper Scanning for Events in Applications and Services Logs
by  Geoff P G  
Go to last post Go to first unread
Last post: 10/20/2021 5:22:11 PM(UTC)