Average Scan Ingestion Stage Time Breakdown Report

Reports (Ad-hoc SQL Queries)

Report Title		Last Updated

Report Title		Last Updated
Average Scan Ingestion Stage Time Breakdown Report		11/8/2020
Description		Notes:
Breakdown report of stages and the average time spent for all PendingScans included in the query. Time Unit: Seconds, unless column headers state otherwise. ThreadFix divides by 1000 to convert milliseconds to seconds.		Outliers can skew averages.
Parameters	Detail	Default
Status filter	To scope down statuses users can use an IN Clause. e.g. : `ps.pendingScanStatus IN ('COMPLETED', ....`)	`ps.pendingScanStatus = 'COMPLETED'`
Number of scans included	The number after the TOP keyword can be adjusted or removed all together; this restricts the number of PendingScans included in this report. Ordering parameters also apply.	`TOP 500`
Order	ASC: earliest scans DESC: latest scans	`ORDER BY createdDate DESC`
SQL Query (MS SQL Server)
/* * Report for avg times of all scan processing phases. * Times are in millisecond. / SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT --TODO: replace avg with mean AVG(CAST(DATEDIFF(millisecond, CONVERT(DATETIME2, ps.createdDate, 103), importing.sTime) AS BIGINT)) AS 'Queued for Import (Scan Files)', AVG( CASE WHEN ps.importType = 'FILE_UPLOAD' THEN CAST(DATEDIFF(millisecond, importing.sTime, queuedForProcessing.sTime) AS BIGINT) ELSE 0 END) AS 'Import Time (Scan Files)', -- Avg time to pull/import a RP PendingScan AVG( CASE WHEN ps.importType = 'REMOTE_PROVIDER_IMPORT_TYPE' THEN CAST(DATEDIFF(millisecond, CONVERT(DATETIME2, ps.requestTime, 103), queuedForProcessing.sTime) AS BIGINT) ELSE 0 END ) AS 'RP Scan Import Time', AVG(CAST(DATEDIFF(millisecond, queuedForProcessing.sTime, processing.sTime) AS BIGINT )) AS 'Queued for Processing', AVG(CAST(DATEDIFF(millisecond, processing.sTime, queueingForIngestion.sTime) AS BIGINT)) AS 'Processing', AVG(CAST(DATEDIFF(millisecond, queueingForIngestion.sTime, queuedForIngestion.sTime) AS BIGINT)) AS 'Queueing for Ingestion', --This might be closer in accuracy than (queuedForReconcile (minus) queuedForIngestion) AVG(CAST(DATEDIFF(millisecond, queueingForIngestion.sTime, queuedForReconcile.sTime) AS BIGINT)) AS 'DB Ingestion', AVG(CAST(DATEDIFF(millisecond, queuedForReconcile.sTime, reconciling.sTime) AS BIGINT)) AS 'Queued for Reconcile', AVG(CAST(DATEDIFF(millisecond, reconciling.sTime, completed.sTime) AS BIGINT)) AS 'Reconcile' FROM (SELECT TOP 500 FROM NewPendingScan WHERE active = 1 and pendingScanStatus = 'COMPLETED' ORDER BY endTime DESC) ps INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'COMPLETED') completed ON completed.pendingScanId = ps.id INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'RECONCILING') reconciling ON reconciling.pendingScanId = ps.id INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_RECONCILE') queuedForReconcile ON queuedForReconcile.pendingScanId = ps.id INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_INGESTION') queuedForIngestion ON queuedForIngestion.pendingScanId = ps.id INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUING_FOR_INGESTION') queueingForIngestion ON queueingForIngestion.pendingScanId = ps.id INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'PROCESSING') processing ON processing.pendingScanId = ps.id INNER JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_PROCESSING') queuedForProcessing ON queuedForProcessing.pendingScanId = ps.id LEFT JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'IMPORTING') importing ON importing.pendingScanId = ps.id LEFT JOIN (SELECT id, pendingScanId, CONVERT(DATETIME2, sentTime, 103) as sTime FROM PendingScanStatusMessage WHERE pendingScanStatus = 'QUEUED_FOR_IMPORT') queuedForimport ON importing.pendingScanId = ps.id; GO