Get an Overview of the Certificates and Whether They Are Self-Signed or Not
Lansweeper scans certificates from your Windows devices by default including information such as the name, issuer, start date, expiration date, certificate algorithm, and more. Due to the ease of self-signed certificates, your IT environment can contain a lot of them. If they are used for critical services or sites, it can be very important to have an overview of them since you’ll have to manage them and ensure the security of the private key. You can find more use cases with certificate data in the Certificates Pro Tips blog post.
The report below provides you with an overview of how many certificates in your environment are self-signed or not.
To use this in a chart widget, prefix the report name with “Chart:” and select it in the chart report widget found on a dashboard.
Windows Self-Signed Certificate Chart Query
Select Distinct Case
When tblCertificates.SubjectName = tblCertificates.IssuerName Then
'Self-signed'
Else 'Not Self-signed'
End As [Certificate type],
Case
When tblCertificates.SubjectName = tblCertificates.IssuerName Then
subquery1.SS
Else subquery2.NSS
End As Total
From tblCertificates
Left Join (Select Count(tblCertificates.Thumbprint) As SS
From tblCertificates
Where tblCertificates.SubjectName = tblCertificates.IssuerName) As subquery1
On 1 = 1
Left Join (Select Count(tblCertificates.Thumbprint) As NSS
From tblCertificates
Where tblCertificates.SubjectName <> tblCertificates.IssuerName) As
subquery2 On 1 = 1
