...
- Overview of jobs showing the counts for each stage (Awaiting Confirmation, Queued, Served, etc.)
Script: Job Stats.sql
Sample Output:
Queued Jobs Analysis:
...
Script: Queued Jobs Analysis.sql
Sample Output:
Jobs Per Server Per Hour
- Script that will display a breakdown of jobs per her per scan engine.
- The script as provided will work for customers with 6 scan engines in their database, there may need to be some interpretation of the results.
- Null values may belong to servers that are not scanning or are used for ui purposes.
Script: JobsPerServerPerHour.sql
Below is what it looks like with 4 scan engines. If there are more scan engines you can add the numbers required to the script.
Image Added
Jobs Per Server Per Hour (dynamic)
Picks up Scan Engine Server names automatically.
| Code Block |
|---|
|
declare @projectID int = 1
exec History.ScanServerProgressHourly @projectId |
Image Added
Stored Procedure sql
| Expand |
|---|
| title | History.ScanServerProgressHourly |
|---|
|
| Code Block |
|---|
| /*
-- Sample execution
declare @projectID int = 1
exec History.ScanServerProgressHourly @projectId
*/
alter procedure History.ScanServerProgressHourly (@projectID int)
WITH ENCRYPTION
as begin
declare
@sqlString1 nvarchar(max) = N'Select [Time], '
,@sqlString2 nvarchar(max) = '
from(
SELECT
dateadd(hour, datediff(hour, 0, StartDate), 0) as [Time]
, Count(*) cnt
, ServerID
FROM jobs.t_job j with(nolock)
inner join jobs.t_JobLocationProjectIPRange jlr with(nolock) on j.JobID = jlr.JobID
where jlr.ProjectID = ' + cast (@ProjectID as nvarchar(5)) +
'GROUP BY dateadd(hour, datediff(hour, 0, StartDate), 0), ServerID
) p
pivot
(
max(cnt)
for serverid in('
,@sqlString3 nvarchar(max) = ')
)piv
Order by Time desc'
;with c_server as
(
select distinct
'[' + cast (s.ServerID as nvarchar(5)) + '] ' [ServerID]
,'''' + s.Hostname + '''' [Hostname]
from config.t_Server s with(nolock)
inner join jobs.t_job j with(nolock) on s.ServerID = j.JobID
)
select distinct
@sqlString1 +=
stuff((select ', ' + ServerID + 'AS ' + Hostname
from c_server
for xml path(''), type)
.value('.','NVARCHAR(MAX)'),1,2,'') --as ServerNames
+ @sqlString2 +
--+ char(13) +
stuff((select ', ' + ServerID
from c_server
for xml path(''), type)
.value('.','NVARCHAR(MAX)'),1,2,'') --as ServerID
+ @sqlString3
from c_server s
print @sqlString1
exec (@sqlString1)
end
go |
|
Job Progress per Scan Server:
| Code Block |
|---|
| language | sql |
|---|
| title | History.ScanServerProgress |
|---|
|
declare @projectID int = 1
exec History.ScanServerProgress @projectId |
Image Added
Stored Procedure sql
| Expand |
|---|
| title | History.ScanServerProgress |
|---|
|
| Code Block |
|---|
| create procedure History.ScanServerProgress (@ProjectID int)
WITH ENCRYPTION
as begin
set nocount on;
declare @msg nvarchar(2000) = N'Procedure name : History.ScanServerProgress' + char(13)
set @msg += N' Returns scanning server overall progress. ' + char(13)
set @msg += N' 1) Servable Targets. ' + char(13)
set @msg += N' 2) Served targets' + char(13)
set @msg += N' 3) Completed percentage' + char(13) + CHAR(13)
set @msg += N' Sample execution:' + char(13)
set @msg += N' declare @proejctId int = 1' + char(13)
set @msg += N' exec History.ScanServerProgress @projectId ' + char(13) + char(13)
set @msg += N' NOTE : Targets which can be served by multiple servers' + char(13)
set @msg += N' will simply be allocated evenly across the each' + char(13)
set @msg += N' server to give the servable_target figure. ' + char(13)
set @msg += N' This may not be how they get served in practice.' + char(13)
raiserror (@msg, 0, 1) with nowait;
with c_serv as
(
select
srv.Hostname
,srv.ServerID
,ips.LocationID
,sum(ips.Target_count) [servable_targets]
,[served].cnt [complete_targets]
from config.fn_IpRangeSummary(@ProjectID, default) ips -- ProjectID :-> 1
inner join config.t__Location_Server ls with ( nolock ) on ips.LocationID = ls.LocationID
inner join config.t_Server srv with ( nolock ) on ls.ServerID = srv.ServerID
left outer join
(
SELECT
j.ServerID
,Count(j.JobID) cnt
FROM jobs.t_job j with(nolock)
GROUP BY ServerID
)
[served] on ls.ServerID = [served].ServerID
group by
srv.Hostname
,srv.ServerID
,ips.LocationID
,[served].cnt
)
-- Locations with multiple Servers assigned
-- Simply divide targets by Server count.
, c_mult_server_per_loc as
(
select
s.LocationID
,count(s.ServerID) [servers_for_location]
from c_serv s
group by s.LocationID
having count(s.ServerID) > 1
)
, c_res as
(
select
s.Hostname
,s.ServerID
,sum(s.[servable_targets] / coalesce(sloc.servers_for_location, 1)) [servable_targets]
,s.[complete_targets]
from c_serv s
left outer join c_mult_server_per_loc sloc on s.LocationID = sloc.LocationID
group by
s.Hostname
,s.ServerID
,s.[complete_targets]
)
select
@ProjectID [ProjectID]
,rank() over (order by r.ServerID) [ServerID]
,r.Hostname [server_name]
,r.servable_targets
,r.complete_targets
,cast(r.complete_targets * 100.00 / r.servable_targets as numeric ( 6, 2)) [complete_pct]
from c_res r
end
go
|
|
Server Throughput vs ActiveJobCount
| Code Block |
|---|
|
declare @projectId int = 1
exec history.ServerThroughput @projectId |
Image Added
| Expand |
|---|
| title | history.ServerThroughput |
|---|
|
| Code Block |
|---|
| create procedure history.ServerThroughput
@ProjectId int
with encryption
as
begin
set nocount on;
declare @msg nvarchar(2000) = N'Procedure name : history.ServerThroughput' + char(13)
set @msg += N' Returns scanning server activity per 10 minute interval.' + char(13)
set @msg += N' 1) Jobs Served. (total over 10 mins)' + char(13)
set @msg += N' 2) ActiveJobCount (average over 10 mins)' + char(13) + CHAR(13)
set @msg += N' Sample execution:' + char(13)
set @msg += N' declare @proejctId int = 1' + char(13)
set @msg += N' exec history.ServerThroughput @proejctId' + char(13)
set @msg += N' '
raiserror (@msg, 0, 1) with nowait;
; with c_Jobs_Served as
(
SELECT
format(StartDate, 'yyyy-MM-dd HH:00:00') as [Time]
, NTILE(6) over(order by datepart(minute, StartDate)) as [time_ntile]
, j.JobID
, ServerID
FROM jobs.t_job j with(nolock)
inner join jobs.t_JobLocationProjectIPRange jlr with(nolock) on j.JobID = jlr.JobID
where jlr.ProjectID = @ProjectId
)
, c_server_active_jobs as
(
select
sm.ServerID
,NTILE(6) over(order by datepart(minute, sm.[Timestamp])) [time_ntile]
,format(sm.[TimeStamp], 'yyyy-MM-dd HH:00:00') [Timestamp]
,sm.ActiveJobCount
from [config].[t_ServerMetric] sm
)
, c_server_active_jobs_summ as
(
select
aj.ServerID
,dateadd(minute, aj.time_ntile * 10, aj.[Timestamp]) [Timestamp]
--,aj.time_ntile
,format(avg(aj.ActiveJobCount * 1.0), 'N2') [ActiveJobCount]
from c_server_active_jobs aj
group by
aj.ServerID
,dateadd(minute, aj.time_ntile * 10, aj.[Timestamp])
)
select
js.ServerID
,dateadd(minute, js.[time_ntile] * 10, js.[Time]) [Time]
,count(js.JobID) [jobs_served]
,aj.ActiveJobCount
from c_Jobs_Served js
left outer join c_server_active_jobs_summ aj on js.ServerID = aj.ServerID
and js.[Time] = aj.[Timestamp]
group by
js.ServerID
,dateadd(minute, js.[time_ntile] * 10, js.[Time])
,aj.ActiveJobCount
order by
[Time]
,js.ServerID
end
go |
|
Job Data Summary:
- Displays server info, current summary of the state of the jobs, and shows active jobs.
...
- Displays the average scan, save and total number of targets per hour
Script: averagescantimesAverage Scan Times.sql
Sample Output:
Job Execution Time Query:
...
