GetCertificateSSL( )
=GetCertificateSSL() 
Check and test SSL certificate information and status in Excel
Returns the values and detail information about the x.509 certificate a target host or URL/URI may provide via an SSL or TLS connection. Gets the certificate. Returns validity, response time, detail or report
Arguments
| TargetURL | Required |
May be URL, Host Name or IP Address. A TCP port number may be specified in the URL. If the argument Port is provided it will take precedence over any port specified in a URL. Any TargetURL must be resolvable by DNS or host file else the return is #ipte_DNS-NORESOLVE
| ReturnType | Optional - default is 2 |
0 TrueFalse = TRUE/FALSE
If a valid certificate is returned from theTargetURL then TRUE is returned else the return is FALSE. Certificate validation is based on local machine policy
1 Time = time in milliseconds
Time taken in milliseconds to receive the response from theTargetURL. The time for name resolutions is not included in the reported time. If the certificate is not retrieved the return is -1
2 CertDetail = certificate field detail
If any certificate is returned by the TargetURL then the element or result selected by CertificateField is returned if defined and present in the certificate otherwise an empty string is returned. Certificate validity is not checked therefore detail from an invalid certificate can be returned
3 CertReport1 = certificate report 1
If a certificate is returned by theTargetURL then certificate report 1 is returned if data is defined and present in the certificate otherwise an empty string is returned
99 FailureReason = error reason
Returns a text string describing the error. If there was no error then the string #ipte_NO-ERROR is returned
| CertificateField | Optional – default is 14 ExpirationDate – Return Type must be 2 |
1 Extension_ApplicationPolicies = Application Policy field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
2 Extension_AuthorityInformationAccess = Authority Information Access field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
3 Extension_AuthorityKeyIdentifier = Authority Key Identifier field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
4 Extension_BasicConstraints = Basic Constraints field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
5 Extension_CertificatePolicies = Certificate Policies field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
6 Extension_CertificateTemplateInformation = Certificate Template Information field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
7 Extension_CRLDistributionPoints = CRL Distribution Points field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
8 Extension_EnhancedKeyUsage = Enhanced Key Usage field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
9 Extension_IssuerAlternativeName = Issuer Alternative Name field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
10 Extension_KeyUsage = Key Usage field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
11 Extension_SubjectAlternativeName = Subject Alternative Name field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
12 Extension_SubjectKeyIdentifier = Subject Key Identifier field in a version 3 x.509 certificate extension
If present in the certificate the field is returned as string
13 EffectiveDate = Date in local time on which a certificate becomes valid
Returns a date type. Ensure the Excel cell format selected is Date
14 ExpirationDate = Date in local time after which a certificate is no longer valid
Returns a date type. Ensure the Excel cell format selected is Date
15 Hash = SHA1 hash value for the certificate as a hexadecimal string
Same as Thumbprint
16 HasPrivateKey = Indicating whether a certificate contains a private key
Returns TRUE or FLASE
17 Issuer = Authority that issued the certificate
Returns the issuer field
18 IssuedBy = Authority that issued the certificate
Returns the CN part of the Issuer field
19 IssuedTo = Entity the certificate is issued to
Same as subject. Returns the entity the certificate is issued to
20 KeyAlgorithm = Key algorithm information
Returns key information
21 KeyAlgorithmParameters = Key algorithm parameter information for the certificate
Returns key algorithm parameter information
22 PrivateKey = Private key
Returns a string that represents the private key
23 PublicKey = Public key
Returns a string that represents the public key
24 PublicKeyEncryption = Public Key encryption algorithm
Return a string that describes the encryption algorithm
25 PublicKeySize = Size of the Public Key
Return a string that describes size of the public key if it can be calculated
26 RawData = Raw data of a certificate
Return the string representation. Must be less than 32k to fit into the Excel cell else #ipte_DATA-TOO-BIG
27 SerialNumber = Serial number of a certificate
Returns a string
28 SignatureAlgorithm = Signature Algorithm
Return the algorithm used to create the signature of a certificate
29 Subject = Subject distinguished name
Return the subject distinguished name from the certificate
30 SubjectName = Subject distinguished name CN part
Same asIssuedTo. Returns the CN part of the Issuer field
31 ThumbPrint = Calculation of thumb print
Gets the thumbprint of a certificate. Dynamically generated using the SHA1 algorithm and does not physically exist in the certificate
32 ThumbPrintAlgorithm = SHA1 is used
Gets the thumbprint of a certificate. Dynamically generated using the SHA1 algorithm and does not physically exist in the certificate
33 Version = X.509 format version
Returns an Integer. Gets the X.509 format version of a certificate
| TimeOut | Optional - default is 10 |
Integer value 1 to 25. Timeout value in seconds. The amount of time to wait for the result from the TargetURL before returning a timeout error
| Port | Optional |
Integer value 1 to 65535. The TCP port number the request will open. If Port is provided it will take precedence over any port specified in a URL
Usage Examples
=GetCertificateSSL (“https://www.cisco.com”)
Creates a connection to the host at https://www.cisco.com on TCP port 443 and then requests a SSL secure connection. If any certificate is returned, the certificate’s expiration date is returned in Excel Date format
=GetCertificateSSL(“https://www.somehostthatisonthenetwork.net:8443”, “TrueFalse”)
Creates a connection to the host at https://www.somehostthatisonthenetwork.net on TCP port 8443 and then requests a SSL secure connection. If any certificate is returned, it is checked against the machine local policy. TRUE is returned if the certificate is valid otherwise FALSE
=GetCertificateSSL(“https://www.godaddy.com”,1,,5)
Creates a connection to the host at https://www.godaddy.com on TCP port 443 and then requests a SSL secure connection. If any certificate is returned within the 5 second timeout, the time taken in milliseconds to receive the certificate response from SomeHost is reported in milliseconds. The time for name resolutions is not included in the reported time so it represents only the time to establish the connection and return the certificate. If the certificate is not retrieved the return is -1
=GetCertificateSSL(A10,3,,,443) where A10 = https://blog.twitter.com/en-gb/uk
Creates a connection to the host at blog.twitter.com on TCP port 443 and then requests a SSL secure connection. If any certificate is returned within the default 10 second timeout, certificate report 1 is returned. Format the cell with the consolas fixed font, wrap cell and set cell height to 409
Notes
Any argument can be a cell reference like A7 or $D$3
Arguments can be entered as “string” or number value
Default timeout for DNS resolution and GetCertificate 10 seconds
The connection’s encryption protocol is negotiated with the server and is selected based on parameters in local machine and operating system policies