cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
WuGe
Champion Sweeper

Hello
I would like to expand the report here to include a field with the thumbprint in which you can search.
Can someone please help me with this, as I don't know what I have to change in the table?

https://www.lansweeper.com/resources/report/operating-system/windows-certificate-overview/

Thank you!

1 ACCEPTED SOLUTION
Mister_Nobody
Honored Sweeper II
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.oscode,
  tblAssets.IPAddress,
  tblAssets.Lastseen As [Last successful scan],
  tblCertificates.SubjectName,
  tblCertificates.IssuerName,
  tblCertificates.Issuer,
  Case
    When tblCertificates.HasPrivateKey = 0 Then 'no'
    Else 'yes'
  End As HasPrivateKey,
  Case
    When tblCertificates.IsArchived = 0 Then 'no'
    Else 'yes'
  End As IsArchived,
  tblCertificates.StartDate,
  tblCertificates.ExpirationDate,
  tblCertificates.Thumbprint,
  tblCertificates.Serial,
  tblCertificates.Subject,
  tblCertificates.FriendlyName,
  tblCertificates.SignatureAlgorithm,
  tblCertificates.DnsNameList,
  tblCertificateLocations.Store,
  Case
    When tblCertificates.ExpirationDate < GetDate() Then '#ff9999'
  End As backgroundcolor
From tblAssets
  Inner Join tblCertificateLocations On tblCertificateLocations.AssetID =
      tblAssets.AssetID
  Inner Join tblCertificates On tblCertificates.CertificateID =
      tblCertificateLocations.CertificateID
Order By tblAssets.AssetName

View solution in original post

2 REPLIES 2
Mister_Nobody
Honored Sweeper II
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.oscode,
  tblAssets.IPAddress,
  tblAssets.Lastseen As [Last successful scan],
  tblCertificates.SubjectName,
  tblCertificates.IssuerName,
  tblCertificates.Issuer,
  Case
    When tblCertificates.HasPrivateKey = 0 Then 'no'
    Else 'yes'
  End As HasPrivateKey,
  Case
    When tblCertificates.IsArchived = 0 Then 'no'
    Else 'yes'
  End As IsArchived,
  tblCertificates.StartDate,
  tblCertificates.ExpirationDate,
  tblCertificates.Thumbprint,
  tblCertificates.Serial,
  tblCertificates.Subject,
  tblCertificates.FriendlyName,
  tblCertificates.SignatureAlgorithm,
  tblCertificates.DnsNameList,
  tblCertificateLocations.Store,
  Case
    When tblCertificates.ExpirationDate < GetDate() Then '#ff9999'
  End As backgroundcolor
From tblAssets
  Inner Join tblCertificateLocations On tblCertificateLocations.AssetID =
      tblAssets.AssetID
  Inner Join tblCertificates On tblCertificates.CertificateID =
      tblCertificateLocations.CertificateID
Order By tblAssets.AssetName

I appreciate your help. Thank you very much!