Custom Report to find what machines DO NOT have a certificate

[heath]

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.

https://community.lansweeper.com/t5/reports-analytics/request-assets-missing-a-specific-certificate/...

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,

[Katgroup]

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

[heath]

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!

[Katgroup]

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