Open Bug 1734497 Opened 7 months ago Updated 3 months ago

Update CA Task List Reports and create monthly email to CAs about their current task list items

Categories

(NSS :: Common CA Database, task, P1)

Tracking

(Not tracked)

ASSIGNED

People

(Reporter: kwilson, Assigned: poonam)

References

(Blocks 1 open bug)

Details

(Whiteboard: [ccadb-roadmap] 2022-Q1)

Per the CCADB Steering Committee meeting on November 4, we would like to do the following:

  1. Update the CA Task List Reports
    -- Which reports to have, their filters, and their columns
  2. Create a monthly email to CAs that is a snapshot of their task list reports.
  3. Turn off the existing audit statement reminder emails, since they will be part of the monthly task list email sent by the CCADB.
  4. Add a tab to the Root Store Operator home page that mirrors the CA Task Lists, but for all CAs. (links to custom reports)

Note: Roadmap Bug #1739529 is about enabling CAs to subscribe to notifications about updates to their Task List items, and providing a report for Root Store Operators to be able to see updates to CA Task Lists.

Assignee: nobody → poonam
Status: NEW → ASSIGNED
Summary: Consolidate Automated Audit Reminder Emails → Update CA Task List Reports and create monthly email to CAs about their current task list items
Blocks: 1739529
Depends on: 1740773

Per the CCADB Steering Committee meeting on December 2, Changes recommended so far:

== Root Certs with Outdated Audit Statements ==

  • Use the Root Certificate Store Summary Status field, and check if any of the statuses there-in contain included or change requested. So the filter logic will not have to be updated later when additional root stores add their statuses.
  • Add the Root Certificate Store Summary Status field column to the report
  • Add light background color-coding to the report
    -- Green - (audit end date < Today - (396 + 30))
    -- Yellow - (audit end date < Today - (396 + 60))
    -- Red - (audit end date < Today - (396 + 90)), bold the text

== Intermediate Certs with Outdated Audit Statements ==

  • Use the Root Certificate Store Summary Status field, and check if any of the statuses there-in contain included or change requested. So the filter logic will not have to be updated later when additional root stores add their statuses.
  • Add the Root Certificate Store Summary Status field column to the report
  • Add light background color-coding to the report
    -- Green - (audit end date < Today - (396 + 30))
    -- Yellow - (audit end date < Today - (396 + 60))
    -- Red - (audit end date < Today - (396 + 90)), bold the text
  • Filter out technically-constrained certs, expired certs, and revoked certs

== Intermediate Certs with no audit information provided ==
== Intermediate Certs with no CP/CPS information provided ==
== Intermediate Certs missing Subordinate CA Owner or Auditor Info ==

  • Combine these 3 reports into one report, with a column indicating what is missing (audit, CP/CPS, subCA name, auditor info)
  • Use the Root Certificate Store Summary Status field, and check if any of the statuses there-in contain included or change requested. So the filter logic will not have to be updated later when additional root stores add their statuses.
  • Add the Root Certificate Store Summary Status field column to the report
  • Filter out technically-constrained certs, expired certs, and revoked certs

== Intermediate Certs with Failed ALV Results ==
== Intermediate Certs with Failed ALV Results for EV SSL ==

  • Combine these 2 reports into one report.
  • Check computation of Derived Trust bits, to add Apple -- look into making this filter independent -- e.g. so only the root cert and intermediate cert logic need to be updated when the other root stores start maintaining this information.
  • Use the Root Certificate Store Summary Status field, and check if any of the statuses there-in contain included or change requested. So the filter logic will not have to be updated later when additional root stores add their statuses.
  • Add the Root Certificate Store Summary Status field column to the report
  • Filter out technically-constrained certs, expired certs, and revoked certs

Columns for combined report:

  • Certificate Name (clickable link)
  • SHA-256 Fingerprint
  • Root Cert Summary Status (all root stores)
  • Audits Same As Parent
  • Audit Statement Dates
    -- Lists Standard, BR, and EV with their audit statement dates
    -- BR and EV dates should be empty if Server Authentication not in Derived Trust Bits
    -- EV date should be empty if not EV capable
  • ALV Found Cert
    -- Lists Standard, BR, and EV with their ALV found cert status of PASS or FAIL or empty (when not applicable)
  • ALV Comments
    -- List Standard, BR, and EV, with each of their ALV Comments (limit displayed text, but show all with hover-over.

== Intermediate Certs with Failed ALV Results for Code Signing ==

  • Use the Root Certificate Store Summary Status field, and check if any of the statuses there-in contain included or change requested. So the filter logic will not have to be updated later when additional root stores add their statuses.
  • Filter out expired certs and revoked certs

== Intermediate Certificates with missing Full CRL ==

  • Use the Root Certificate Store Summary Status field, and check if any of the statuses there-in contain included or change requested. So the filter logic will not have to be updated later when additional root stores add their statuses.
  • Filter out expired certs and revoked certs
Duplicate of this bug: 1731100
Whiteboard: [ccadb-roadmap] 2021-Q4 → [ccadb-roadmap] 2022-Q1

Following changes have been done in sandbox:

  • A new formula field "Root Certificate Included?" has been added to determine if the certificate is included by any of the root stores. This field will be used in reports and programs to avoid future code changes when a new root store is added.

  • Please check the reports below to see the results generated by the new formula field:
    "Root Certificate Included in Any RS"
    "Intermediate Cert Included in Any RS"

  • On homepage, please scroll to the bottom to see a new custom component "Root Certs with Outdated Audit Statements". This sample shows data based on the logged in user. For a Root Store all records will be visible whereas for a CA only the records that are associated with the CA Owner are visible.

Rest of the task list sections will be added after I have your feedback.

(In reply to Poonam Bhargava from comment #3)

Following changes have been done in sandbox:

  • A new formula field "Root Certificate Included?" has been added to determine if the certificate is included by any of the root stores. This field will be used in reports and programs to avoid future code changes when a new root store is added.

  • Please check the reports below to see the results generated by the new formula field:
    "Root Certificate Included in Any RS"
    "Intermediate Cert Included in Any RS"

I checked both of these reports, and the results of "Root Certificate Included?" looks good.

  • On homepage, please scroll to the bottom to see a new custom component "Root Certs with Outdated Audit Statements". This sample shows data based on the logged in user. For a Root Store all records will be visible whereas for a CA only the records that are associated with the CA Owner are visible.

Please don't list the certificate if (Today - Audit Period End date) < 365

And update logic and the information at the top of the report to:

This report shows all Root Certificates which satisfy the criteria below.

  • Certificate is Included in participating Root Stores
  • Certificate is not expired
  • Highlighted in Green: 365 < (Today - Audit Period End date) < 425
  • Highlighted in Yellow: 425 < (Today - Audit Period End date) < 455
  • Highlighted in Red: 455 < (Today - Audit Period End date)

Notes from the CCADB Steering Committee meeting on January 13, 2022...

  • For root store home pages, create a new tab called “CA Task Lists” to contain the new reports that are currently underneath the “Root Store Task List”.
  • For all of the new reports:
    • On each of the new reports, add a filter to allow users to filter on Root Store. For example, when “Apple” is selected, only show the results for which the Root Certificate Status contains “Apple: Included”
    • Add indications to the header text bullet points to distinguish between the AND & OR filters
    • Enable sort by clicking on an any column headings in the reports
    • Change “Certs” to “Certificates” in the report headings/names
  • For report: Root Certificates with Outdated Audit Statements
    • Add a column named “Audit Case” which contains a link to the open Audit Case associated with the root certificate, if there is one.
  • For these reports: Intermediate Certificates with Outdated Audit Statements, Intermediate Certificates with Missing Information, and Intermediate Certificates with Failed ALV Results
    • Remove “Technically Constrained” filters, and update the header text bullet points to remove “Technically Constrained is FALSE”
    • Add a column for Technically Constrained (it should be after Derived Trust Bits)
    • Add a filter to allow users to filter on Derived Trust Bits. For example, when “Server Authentication” is selected, only show the results for which the Derived Trust Bits contains Server Authentication.
  • For report: Intermediate Certificates with Failed ALV Results
    • The ALV results did not get copied from production into Sandbox for many of the intermediate certificates.
      • Compare the Intermediate Certs - Failed ALV Results For Standard and BR report in production with this report, and take a look at the intermediate certificate records that are giving different results
      • Can resolve by running ALV over intermediate certs in sandbox that are missing the ALV results. (we can test the ALV comments in sandbox by adding our own)
    • The dates in the Audit Statement Dates column should come from wherever the audit statement is found (e.g. may be found higher up in the certificate chain)

== Email ==
We would like to replace all of the separate audit reminder emails with one monthly email from the CCADB.
To start with, the email will look like:
~~
From: noreply@ccadb.org
TO: <Primary POC>
CC: <POCs, only if Alias1 and Alias2 null> and <Alias1 & Alias2>

Subject:
If the two audit statement reports are non-zero, then "CCADB: Overdue Audit Statements"
Otherwise "CCADB: Items Need Your Attention"

You have the following items that need to be resolved in the CCADB. Please login to the CCADB to see the full list on your home page.

  • Root Certificates with Outdated Audit Statements (5)
  • Intermediate Certificates with Outdated Audit Statements (2)
  • Intermediate Certificates with Missing Information (0)
  • Intermediate Certificates with Failed ALV Results (6)
  • Intermediate Certificates with Missing Full CRL (145)

Instructions may be found at www.ccadb.org/cas.
If you need help, contact support@ccadb.org or the appropriate root store email address that is listed on your CCADB home page.

Regards,
CCADB Team

~~

Then we’ll request feedback from CAs and may add more information to the emails later.

I have added column sort ability on 'Root Certificates with Outdated Audit Statements'. When you hover over the report columns, the arrow indicator can be switched to ascending or descending. A filter on Root Store is also available now. Please check these feature and let me know if I can proceed to add them to the rest of the reports.

(In reply to Kathleen Wilson from comment #5)

  • For root store home pages, create a new tab called “CA Task Lists” to contain the new reports that are currently underneath the “Root Store Task List”.

This has been done -- Can now see the new task lists for CA under "CA Task Lists"

(In reply to Poonam Bhargava from comment #6)

I have added column sort ability on 'Root Certificates with Outdated Audit Statements'. When you hover over the report columns, the arrow indicator can be switched to ascending or descending. A filter on Root Store is also available now. Please check these feature and let me know if I can proceed to add them to the rest of the reports.

I'm not seeing this. Maybe it's not enabled for standard internal users yet?

(In reply to Kathleen Wilson from comment #7)

(In reply to Kathleen Wilson from comment #5)

  • For root store home pages, create a new tab called “CA Task Lists” to contain the new reports that are currently underneath the “Root Store Task List”.

This has been done -- Can now see the new task lists for CA under "CA Task Lists"

(In reply to Poonam Bhargava from comment #6)

I have added column sort ability on 'Root Certificates with Outdated Audit Statements'. When you hover over the report columns, the arrow indicator can be switched to ascending or descending. A filter on Root Store is also available now. Please check these feature and let me know if I can proceed to add them to the rest of the reports.

I see this now. Looks good.

(In reply to Kathleen Wilson from comment #8)

(In reply to Kathleen Wilson from comment #7)

(In reply to Kathleen Wilson from comment #5)

  • For root store home pages, create a new tab called “CA Task Lists” to contain the new reports that are currently underneath the “Root Store Task List”.

This has been done -- Can now see the new task lists for CA under "CA Task Lists"

(In reply to Poonam Bhargava from comment #6)

I have added column sort ability on 'Root Certificates with Outdated Audit Statements'. When you hover over the report columns, the arrow indicator can be switched to ascending or descending. A filter on Root Store is also available now. Please check these feature and let me know if I can proceed to add them to the rest of the reports.

I see this now. Looks good.

I also see the "Filter on Root Store", and that is great!

Even though our focus has been on updating the other reports, please remember to also keep these two reports:

  • Open Cases for My CA
  • Contacts Who May be Obsolete

(In reply to Kathleen Wilson from comment #10)

Even though our focus has been on updating the other reports, please remember to also keep these two reports:

  • Open Cases for My CA
  • Contacts Who May be Obsolete

Added the above reports to CA Task List. Also added sort capability and filters to the intermediate cert reports.

Looks good. Please also make the following changes.

Report: Open Cases

  • Limit the text in the "Latest Case Comment" column to 100 characters, and use hover-over to display the full text.

Report: Intermediate Certificates with Failed ALV Results

  • The criteria in the description has: "Certificate is Audits Same as Parent is FALSE and OneCRL status is not 'Added to OneCRL'". Please make sure this is removed from both the filter logic and the description.
  • Limit the text in the "ALV Comments" column to 100 characters, and use hover-over to display the full text.

Report: Contacts who may be obsolete

  • Change "This report shows all contacts who may obsolute." to provide information about the filters used to generate the report.

(In reply to Kathleen Wilson from comment #12)

Looks good. Please also make the following changes.

Report: Open Cases

  • Limit the text in the "Latest Case Comment" column to 100 characters, and use hover-over to display the full text.

Report: Intermediate Certificates with Failed ALV Results

  • The criteria in the description has: "Certificate is Audits Same as Parent is FALSE and OneCRL status is not 'Added to OneCRL'". Please make sure this is removed from both the filter logic and the description.
  • Limit the text in the "ALV Comments" column to 100 characters, and use hover-over to display the full text.

Report: Contacts who may be obsolete

  • Change "This report shows all contacts who may obsolute." to provide information about the filters used to generate the report.

We are not able to add hover feature on 'Comments' column, but we will continue to try. The column is displaying 'Clip text' as default. It can be switched to 'Wrap text' if you need to see the full text.

The text at the top of each report have been changed to explain the filters used used in the custom reports.

ALV was rerun for all intermediate certs in sandbox and ALV Comment fields were populated from production.

Rest of the modifications are also complete as per your request in Comment #12.

For the "Intermediate Certificates with Failed ALV Results" report, please change the column heading "Certificate Not Found in Audits" to "Audits Omitting Certificate".

(In reply to Poonam Bhargava from comment #13)

We are not able to add hover feature on 'Comments' column, but we will continue to try. The column is displaying 'Clip text' as default. It can be switched to 'Wrap text' if you need to see the full text.

How about if you switch to "Wrap Text" but limit the number of characters to 150?

The text at the top of each report have been changed to explain the filters used used in the custom reports.

Looks good

ALV was rerun for all intermediate certs in sandbox and ALV Comment fields were populated from production.

Thanks

Rest of the modifications are also complete as per your request in Comment #12.

Thanks

All of the changes look great!

When you are ready, please proceed with moving these new CA Task List Reports to production -- but only visible to root store operators to start with.

Thanks!

Please also update the "Contacts who may be obsolete" report to have a "Status of CA Owner" column (between CA owner and Contact Name). And add the ability to "Filter on Root Store" based on the new column.

Added filter to the "Contacts who may be obsolete" report and added the "Status of CA Owner" column.

The "CA Task List" tab is now visible to all root store on their homepage as well as on the CA Owner page. The collapsed section headers of the report look difference in sandbox vs production UI, I am currently working on it.

Looks great!

In the "Contacts who may be obsolete" report, please add a column (at the end) for "Last Modified Date". This will help us determine if action is needed for those Contacts who have never logged in.

"Last Login on" is blank for those who have never logged in. Instead I replace blank with text "Never logged in"?

Either way is fine -- blank or "Never logged in".

The problem I'm having is that we may have granted their access recently -- in which case, they are not obsolete. I don't want to have to check each Contact record to see how recently they were granted access.

Also, in the "Contacts who may be obsolete" report please also filter out the Contacts who are already set to Obsolete.

I have updated "Contacts who may be obsolete" report. The last column will show "Never logged in. Created on m/d/yyyy." when a CA has never logged in. Also the report excludes Contacts with Type = "Obsolete" or "Other".

Looks good.

I updated the CCADB Release Notes.

Now that the new task list reports are completed and in production, we will monitor in the CCADB Steering Committee to determine when to switch over to the new monthly email.

You need to log in before you can comment on or make changes to this bug.