Hi,
We have created a sample report for the info you are asking for .
Instructions for adding this report to your Lansweeper installation can be found here: https://www.lansweeper.com/knowledgebase/how-to-add-a-report-to-your-lansweeper-installation/
If you are interested in building or modifying reports, we do recommend:
• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial: https://www.w3schools.com/sql/
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here: https://www.lansweeper.com/knowledgebase/accessing-the-lansweeper-database-documentation/
Checking out our report library: https://www.lansweeper.com/report/
SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.OScode
FROM
tblAssets
INNER JOIN
TsysAssetTypes ON tblAssets.Assettype = TsysAssetTypes.AssetType
WHERE
TsysAssetTypes.AssetTypename = 'Windows'
AND NOT EXISTS (
SELECT 1
FROM tblCertificateLocations AS CertLoc -- Using the table name you provided
INNER JOIN tblCertificates
ON CertLoc.CertificateID = tblCertificates.CertificateID
WHERE
CertLoc.AssetID = tblAssets.AssetID
AND tblCertificates.Subject LIKE '%Cisco Secure Access Root CA%'
AND CertLoc.Store = 'Root'
)
ORDER BY
tblAssets.Domain,
tblAssets.AssetName;