Scan Ingestion Percentile Throughput Report

Reports (Ad-hoc SQL Queries)

Report Title		Last Updated

Report Title		Last Updated
Scan Ingestion Percentile Throughput Report		11/8/2020
Description		Notes:
A scan throughput report in percentile/end-2-end ingestion time report. Time Unit: Seconds, unless column headers say otherwise. ThreadFix divides by 1000 to convert milliseconds to seconds.		Limits results to the latest 1000 PendingScan that have the active flag set to “True”. The system currently never sets the PendingScan flag to “False”.
Parameters	Detail	Default
Active Filter	This column currently remains `true` unless user modified via a SQL update script.
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 1000`
Order	ASC: earliest scans DESC: latest scans	`ORDER BY createdDate DESC`
SQL Query (MS SQL Server)
/** * Scan Ingestion Processing Time Percentile Report * A scan throughput report in precentile/end-2-end ingestion time reports * Default: limits results to latest 1000 PendingScan that active flag is true/hasn't been user modified * Parameters: * Count limit: change the number after the TOP keyword, you can also remove it all together. (Default: TOP 1000) * Order: switch DESC to ASC for earliest scans. (Default: ORDER BY createdDate DESC) * Active / SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT status AS Status, COUNT() AS total, AVG(twentyfifthPTime) AS '25th % Processing Time (Sec)', AVG(medianPTime) AS 'Median Processing Time (Sec)', AVG(eightiethPTime) AS '80th % Processing Time (Sec)', AVG(ninetiethPTime) AS '90th % Processing Time (Sec)', AVG(ninetyfifthPTime) AS '95th % Processing Time (Sec)' FROM (SELECT status, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY processingTime ASC) OVER (PARTITION BY status) AS medianPTime, PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY processingTime ASC) OVER (PARTITION BY status) AS twentyfifthPTime, PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY processingTime ASC) OVER (PARTITION BY status) AS eightiethPTime, PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY processingTime ASC) OVER (PARTITION BY status) AS ninetiethPTime, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY processingTime ASC) OVER (PARTITION BY status) AS ninetyfifthPTime FROM (SELECT TOP 1000 -- this can be adjusted pendingScanStatus AS status, createdDate, endTime, (DATEDIFF(millisecond, CONVERT(DATETIME2, createdDate, 103), CONVERT(DATETIME2, modifiedDate, 103))/1000) AS processingTime FROM NewPendingScan WHERE active = 1 ORDER BY createdDate DESC) ps) report GROUP BY status; GO