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,

[heath]

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!

[Katgroup]

Edit: Splitting this up into separate posts since it's having issues in one post:

Here's the testing I performed:

1. Create a self-signed certificate on a test machine. Scan with Lansweeper and note the certificate's serial.
   • See separate post.
2. Report for all computers without the certificate. Note the serial will be different in your case:
   • See separate post.
3. 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.

[Katgroup]

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

[Katgroup]

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

[Katgroup]

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

[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