Report Question

[nknippen]

Good afternoon,

After having bought your excellent product yesterday afternoon I have tried to play around with the SQL queries to generate some new reports.

Some standard reports such as: Workstation: All workstations without Antivirus are very interesting but I am missing in this report the display name (which is more useful than just the computer account).

Which SQL query string do I need to add to your existing query in order to achieve this?

Here is the mentioned example I spoke about:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Lastseen,
tsysOS.Image As icon
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Inner Join tsysantivirus On tblSoftwareUni.softwareName
Like tsysantivirus.Software) And tblAssets.AssetID Not In (Select
tblAntivirus.AssetID
From tblAntivirus) And tblComputersystem.Domainrole <= 1 And
tblAssetCustom.State = 1


Looking forward to your comments.

Regards,

Nila

[Hemoco]

The report above only lists the last user that logged into the machine, not all users. A complete list of user logons can only be found in tblCPlogoninfo, which is not included in the report above.

[nknippen]

Hello Lansweeper,

If I try this then I see all the users that ever logged onto this computer in the report. Is there anyway to just select the last logged on user? In which table would I find that entry?

Regards,

Nils

[Hemoco · Accepted solution]

Link tblAssets to tblADusers on both the Username and Userdomain field to get the computer user's AD display name, if this is what you're asking. E.g.:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Lastseen,
tsysOS.Image As icon,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Displayname
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Inner Join tsysantivirus On tblSoftwareUni.softwareName
Like tsysantivirus.Software) And tblAssets.AssetID Not In (Select
tblAntivirus.AssetID
From tblAntivirus) And tblComputersystem.Domainrole <= 1 And
tblAssetCustom.State = 1