Windows Self-Signed Certificate Overview

Select tblCertificates.SubjectName,
tblCertificates.IssuerName,
tblCertificates.FriendlyName,
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.SignatureAlgorithm,
Count(tblCertificates.Thumbprint) As Total,
tblCertificates.StartDate,
tblCertificates.ExpirationDate
From tblCertificates
Inner Join tblCertificateLocations On tblCertificateLocations.CertificateID =
tblCertificates.CertificateID
Where tblCertificates.SubjectName = tblCertificates.IssuerName
Group By tblCertificates.SubjectName,
tblCertificates.FriendlyName,
tblCertificates.IssuerName,
tblCertificates.HasPrivateKey,
tblCertificates.IsArchived,
tblCertificates.StartDate,
tblCertificates.ExpirationDate,
tblCertificates.SignatureAlgorithm
Order By Total Desc