
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-26-2025 06:14 PM
Hello,
I have been searching and searching and I cannot find a custom report to help us find what domain machines DO NOT have a specific certificate installed on them. We deploy the cert via GPO and we know are going to have outliers for various reasons. We are trying to find out who we need to reach out to before we make some changes that will break certain access for people missing the certificate.
I tried the below, and adjusted the serial number for our cert but had no luck, as in it gave me some very strange results.
I tried some AI stuff but nothing their worked either. I was hoping someone out there might have already created something similar etc.
Any input is greatly appreciated.
Thanks,
- Labels:
-
Report Center

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-31-2025 09:53 PM
Sorry for my delay, thank you very much....should this be showing all devices that do not have the cert....seems like it's showing all that do.
Thank you again!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-31-2025 11:26 PM - edited ‎03-31-2025 11:31 PM
Edit: Splitting this up into separate posts since it's having issues in one post:
Here's the testing I performed:
- Create a self-signed certificate on a test machine. Scan with Lansweeper and note the certificate's serial.
- See separate post.
- Report for all computers without the certificate. Note the serial will be different in your case:
- See separate post.
- Report for all computers with certificate installed. Note the serial will be different in your case:
- See separate post.
Both reports seem to work according to the above testing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-31-2025 11:32 PM
Report for all computers with certificate installed. Note the serial will be different in your case:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID In (Select Top 1000000 tblCertificateLocations.AssetID
From tblCertificates Inner Join tblCertificateLocations On
tblCertificates.CertificateID = tblCertificateLocations.CertificateID
Where tblCertificates.Serial = '3ED1D1FA21EE3C9F43B574E438BE56D5')
And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-31-2025 11:32 PM
Report for all computers without the certificate. Note the serial will be different in your case:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
tblAssets.AssetID Not In (Select Top 1000000 tblCertificateLocations.AssetID
From tblCertificates Inner Join tblCertificateLocations On
tblCertificates.CertificateID = tblCertificateLocations.CertificateID
Where tblCertificates.Serial = '3ED1D1FA21EE3C9F43B574E438BE56D5')
And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-31-2025 11:31 PM
Create a self-signed certificate on a test machine. Scan with Lansweeper and note the certificate's serial.
New-SelfSignedCertificate -Subject "CN=TestCertificate" -CertStoreLocation "Cert:\LocalMachine\My" -KeyExportPolicy Exportable -KeySpec Signature -KeyLength 2048 -KeyAlgorithm RSA -HashAlgorithm SHA256
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-28-2025 10:08 PM
The linked report from @Mister_Nobody seems to work fine for me. Here's a test I used to show all assets with VeriSign Class 3 installed.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblCertificateLocations On
tblAssets.AssetID = tblCertificateLocations.AssetID
Left Join tblCertificates On tblCertificates.CertificateID =
tblCertificateLocations.CertificateID
Where
tblAssets.AssetID Not In (Select Top 1000000 tblCertificateLocations.AssetID
From tblCertificates Inner Join tblCertificateLocations On
tblCertificates.CertificateID = tblCertificateLocations.CertificateID
Where tblCertificates.Serial = '18DAD19E267DE8BB4A2158CDCC6B3B4A')
And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-28-2025 10:17 PM
Thank you so much for your time! I created a report with your info and our certs thumbprint, and it runs but we get endless repeats of the same devices over and over, similar to the other report I linked to.
Any idea what could be causing that? No other of our reports are doing that...again thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-28-2025 10:58 PM
Shooting in the dark here, but try this...
Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
tblAssets.AssetID Not In (Select Top 1000000 tblCertificateLocations.AssetID
From tblCertificates Inner Join tblCertificateLocations On
tblCertificates.CertificateID = tblCertificateLocations.CertificateID
Where tblCertificates.Serial = '18DAD19E267DE8BB4A2158CDCC6B3B4A')
And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
