Reports (Ad-hoc SQL Queries)
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 | |
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. |
|
Order | ASC: earliest scans DESC: latest scans |
|
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 |