Report Login time reduces number of computers by 300

[cross_eur]

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%'

[RCorbeil]

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:

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:

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:

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]

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.

RC62N wrote:
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:

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:

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:

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%'