Urgent request to support quarterly reporting by 7/24 - Signal Detector Status Updates

[atdservicebot]

What application are you using?

AMD Data Tracker

Describe the problem.

Urgent request to support quarterly reporting by 7/24

We need to adjust the "Intersections with All Detectors Working" metric and how each intersection is reporting its status of "OK" or "Broken" overall. We added new detection status that should cause the signal to be listed as "Broken" but are listed as "OK" because no detector has a broken status. Instead they have a "Replace" or "Investigation" status which means they are in our queue for troubleshooting and repair. However, they are still broken.

Describe the outcome you'd like to see when this feature is implemented.

Update how that column gets reported so our metrics will be correct again.

Describe any workarounds you currently have in place or alternative solutions you've considered.

None.

Is there anything else we should know?

7/24 for quarterly executive report

Requested By Lance B.

Request ID: DTS24-116265

[lanceballard]

Hi friends. I'm checking on this while Brian is out. Do we have an anticipated time y'all can get to this?

Thanks!

[ChristinaTremel]

In scoping out this request, I've found detector statuses on the signals object and the signal_detectors object

Detector Status options on signal_detectors object:

• OK
• OK - MINOR ISSUE
• LVL 1 INVESTIGATION
• LVL 2 INVESTIGATION
• REPLACE
• BROKEN
• UNKNOWN
• REMOVED
• VERIFICATION NEEDED
• INACTIVE

Detection_Status options on the signals object are:

• Broken
• No Detection
• Ok
• Unknown

Lance reviewed these lists and mapped out how the detector statuses on the signal_detectors object should map to the statuses on the signals object.

signal_detectors object statuses	signal object statuses
OK	Ok
OK - MINOR ISSUE	Ok
LVL 1 INVESTIGATION	Broken
LVL 2 INVESTIGATION	Broken
REPLACE	Broken
BROKEN	Broken
UNKNOWN	Unknown
REMOVED	No Detection
VERIFICATION NEEDED	Unknown
INACTIVE	No Detection

[ChristinaTremel]

Proposed Solution

Add number fields to the signals_detectors object:

• Status Broken:
  • if status is LVL 1 INVESTIGATION, LVL 2 INVESTIGATION, REPLACE, or BROKEN --> set custom value to 1
  • if not these statuses --> set custom value to 0
• Status Unknown
  • if status is UNKNOWN, VERIFICATION NEEDED --> set custom value to 1
  • if not these statuses --> set custom value to 0
• Status No Detection
  • if status is REMOVED, INACTIVE--> set custom value to 1
  • if not these statuses --> set custom value to 0
• Status Ok
  • if status is OKAY, OKAY - MINOR ISSUE--> set custom value to 1
  • if not these statuses --> set custom value to 0

Add sum fields to signals object

• SUM_DETECTOR_BROKEN
  • Field to sum is new Status Broken field on signals_detectors object
• SUM_NO_DETECTORS (already exists), but need to look at how No Detectors is currently being captured on the signals_detectors object
• SUM_DETECTOR_OK
  • Field to sum is new Status Ok field on signals_detectors object
• SUM_DETECTOR_UNKNOWN
  • Field to sum is new Status Unknown field on signals_detectors object

@dianamartin I'm going to walk through this with you during our work session today to confirm that this woud be the right direction! I referenced the way we update statuses on markings work orders with job statuses.

[ChristinaTremel]

7/23

Add number fields to the signals_detectors object:

• [x] Status Broken:
  • if status is LVL 1 INVESTIGATION, LVL 2 INVESTIGATION, REPLACE, or BROKEN --> set custom value to 1
  • if not these statuses --> set custom value to 0
• [x] Status Unknown
  • if status is UNKNOWN, VERIFICATION NEEDED --> set custom value to 1
  • if not these statuses --> set custom value to 0
• [x] Status No Detection
  • if status is REMOVED, INACTIVE--> set custom value to 1
  • if not these statuses --> set custom value to 0
• [x] Status Ok
  • if status is OKAY, OKAY - MINOR ISSUE--> set custom value to 1
  • if not these statuses --> set custom value to 0

Add sum fields to signals object

• [x] SUM_DETECTOR_BROKEN
  • Field to sum is new Status Broken field on signals_detectors object
• [x] SUM_NO_DETECTORS (already exists), but need to look at how No Detectors is currently being captured on the signals_detectors object
• [x] SUM_DETECTOR_OK
  • Field to sum is new Status Ok field on signals_detectors object
• [x] SUM_DETECTOR_UNKNOWN
  • Field to sum is new Status Unknown field on signals_detectors object

[ChristinaTremel]

Hierarchy of status updates

• Broken (if one detector is broken, the entire intersection is flagged as broken)
• Unknown (if there's no broken detectors, but one detector is unknown and the other is in one of the other statuses below, the entire intersection is flagged as unknown)
• Ok (if there's no broken or unknown detectors, the intersection is flagged as Ok)
• No Detection (if there's no broken, ok, or unknown detector statuses, then there's no detection)

[ChristinaTremel]

Current Count of Detector Statuses in the sum fields:

• NO STATUS - 433
• OK STATUS - 316
• BROKEN STATUS - 183
• UNKNOWN STATUS - 13

Old Count of Detector Statuses using Detector Status field:

• NO - 266
• OK STATUS - 766
• BROKEN STATUS - 250
• UNKNOWN STATUS - 9

Additional Info:

• When exploring how the status is updates currently, we found that there's a python script that's updating the detector statuses on the signal object. - #5987

[ChristinaTremel]

• Created new DETECTOR STATUS 2024 field in the signals object
• Added conditional rules for updating the status:

[ChristinaTremel]

7/29

• Checked in with Lance today and got a few of my questions answered about the statuses and how they should be updated
• Checked in with John about the python script and he noted:

  it might be that Knack didn’t have the functionality we needed way back when i created that script. the script just checks all detectors at the signal and sets the signal detector status as broken if any of the detectors are broken.

• QCed several records to confirm the statuses were updating and noticed the SUM fields were buggy and not summing the detection statuses correctly. Reached out to Chrispin to help me troubleshoot and we decided it would be best to create COUNT fields instead.
• Once creating the count fields, I was QCing and found that the counts were accurate and were updating the status as expected!
• The plan is for John to turn off the existing Python script that's updating the existing DETECTION_STATUS field. Once turned off, we will add the conditional rules to this field that are currently on the DETECTION STATUS 2024 field.
• Coordinated with Chia and she will do a full replacement of the data in the ODP dataset! to ensure the statuses are updated correctly 🎉

[ChristinaTremel]

• Exported a copy of the signals object to archive
• John disabled the python script updating statuses
• I added the conditional rules to the DETECTION_STATUS
• Reimported the previous statuses to the DETECTION STATUS 2024 field and exported the signals where the statuses differ so Lance can review and determine if he'd like to change the conditional rules at all
• Chia did a full replacement of the data in the ODP dataset and I confirmed that the updated statuses are reflected ✅

[ChristinaTremel]

Reviewed this during the AMD sprint review and we are good to close this out as complete! If any changes are needed, we will ask the MMC to submit a new ticket.