- Created by Daniel Colon on Jun 25, 2021
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
Version 1 Current »
📙 You will learn
About the Various available Scan Ingestion Reports.
Prerequisites
Audience: IT Professional
Difficulty: Intermediate
Time needed: Approximately 10 minutes
Tools required: database client/access
Reports (Ad-hoc SQL Queries)
Table of contents
Scan Ingestion Percentile Throughput Report
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 latest 1000 PendingScan that have 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 |
Total Scan Ingestion Time Report
Report Title | Last Updated | |
---|---|---|
Total Scan Ingestion Time Report | 11/8/2020 | |
Description | Notes: | |
Total start to finish time for all Completed and Active Pending Scans |
| |
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) | ||
/* * Total start to finish time for all 'Completed' and 'active' Pending Scans * Result time unit: minutes */ SELECT (DATEDIFF(millisecond, CONVERT(DATETIME2, oldestCreatedDate, 103), CONVERT(DATETIME2, latestEndDate, 103))/1000 / 60) AS totalTime FROM (SELECT MIN(createdDate) AS oldestCreatedDate, MAX(modifiedDate) AS latestEndDate FROM (SELECT TOP 1000 * FROM NewPendingScan WHERE active = 1 AND pendingScanStatus = 'COMPLETED' ORDER BY createdDate DESC) ps ) t ; |
Average Scan Ingestion Stage Time Breakdown Report
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. | Outliers can skew averages. | |
Parameters | Detail | Default |
Status filter | To scope down statuses users can use an IN Clause. e.g. : |
|
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) | ||
/* * 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
|
Remote Provider Import Request Time
Report Title | Last Updated | |
---|---|---|
Remote Provider Import Request Time | 11/8/2020 | |
Description | Notes: | |
Report of Remote Provider Import requests initiated by a user or scheduler. Requests can be: bulk imports, import for a single Remote Provider application or imports from all Remote Provider applications mapped to a ThreadFix application. Column Definitions:
|
| |
Parameters | Detail | Default |
|
|
|
SQL Query (MS SQL Server) | ||
SELECT rpe.name AS 'RP Connection', rpt.name AS 'Source Type', type AS 'Request Type', status AS 'Status', (DATEDIFF(millisecond, CONVERT(DATETIME2, processingStartTimestamp, 103), COALESCE(CONVERT(DATETIME2, finishedTimestamp, 103), GETDATE())) / (1000 * 60)) as 'Import Time (min)', requestTimestamp AS 'Requested Time', processingStartTimestamp AS 'Request Import Start Time', finishedTimestamp AS 'Request Import Finish Time', applicationId, remoteProviderApplicationId, error, errorMessage, request.id FROM RemoteProviderImportRequest request LEFT JOIN RemoteProviderEntity rpe ON rpe.id = request.remoteProviderEntityId LEFT JOIN RemoteProviderType rpt ON rpt.id = rpe.remoteProviderTypeId ORDER BY createdDate DESC; |
- No labels