Table columns for sav_sslcert

[Manu269]

Hello team,

Can someone help in providing the columns for sav_sslcert table?

Also can someone help in confirming that we see an issue on ui (certificate management) where the certificate does shows expired whereas the connection is working perfectly.

[rushikeshvartak]

• if certificate is uploaded from UI then expiry date gets stored in database. 
• if certificate was uploaded directly on server database table date will not be up to date.
  • PFB column

  • TABLE_NAME	COLUMN_NAME
    sav_sslcert	ALIAS
    sav_sslcert	CERT_KEY
    sav_sslcert	ISSUERNAME
    sav_sslcert	STATUS
    sav_sslcert	SUBJECTNAME
    sav_sslcert	UPDATE_DATE
    sav_sslcert	VALID_FROM
    sav_sslcert	VALID_TO

[Manu269]

Hello,

Is there a way to identify how certificate was uploaded?

Also, we see difference in number of results returned from query and from ui. Any specific reason?

[rushikeshvartak]

Check for status active

[Manu269]

We see some issue again.

On running the query we see inspite some certificate expiry date has passed the statuskey show 1.

And also which are active some show status 2.

There is also difference in the number of results returned and what displayed via query.

Is this a bug?

[rushikeshvartak]

Share sample result

[rushikeshvartak]

Its shows correct in my case.

[Manu269]

As per your screenshot last entry is showing 2. It's still valid cert right?

Similarly 1 St entry is showing 1 for active.

[rushikeshvartak]

Certificate is uploaded multiple times hence its valid

[Manu269]

But doesn't it shows the discrepancy?

[rushikeshvartak]

No

[Manu269]

my point here is its gives a confusion if it has expired or not.

Also we dont undertsand or there is no clarity it has gone multiple uploads

[rushikeshvartak]

Every certificate have single active entry check latest update date

SELECT
ALIAS,
CERT_KEY,
ISSUERNAME,
STATUS S,
SUBJECTNAME,
UPDATE_DATE,
VALID_FROM,
VALID_TO
FROM
sav_sslcert where alias ='test_certificate'

[Manu269]

For us it's an issue.

Logged a ticket.

[sudeshjaiswal · Accepted solution]

Hello @Manu269  and @rushikeshvartak,

The status shown in the UI and the status stored in the database table are two different entities. The UI calculates the expiry date from VALID_FROM,
VALID_TO to determine whether the status of the certificate is expired or active and show it on UI.

In the database, the status column is represented by 1 and 2:

- 1 means active (visible in the list and available in the trust store)

- 2 means deleted from the trust store and the UI list, but retained in the database for audit purposes.

To determine the current status of the certificate, you need to form the query based on the VALID_FROM & VALID_TO to determine whether the status is expired or active columns.

Thanks,
Sudesh

[rushikeshvartak]

SELECT
ALIAS,
CERT_KEY,
ISSUERNAME,
CASE
WHEN STATUS = 2 THEN 'Deleted'
WHEN CURRENT_DATE < VALID_FROM THEN 'Not Yet Valid'
WHEN CURRENT_DATE > VALID_TO THEN 'Expired'
WHEN CURRENT_DATE BETWEEN VALID_FROM AND VALID_TO THEN 'Active'
END AS CURRENT_STATUS,
SUBJECTNAME,
UPDATE_DATE,
VALID_FROM,
VALID_TO
FROM
sav_sslcert
WHERE
alias = 'test_certificate';

[Manu269]

Hi @sudeshjaiswal @rushikeshvartak thanks for the details.

Let me also explore the ootb analytics provided by Saviynt.