Versions Compared

Old Version 11

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Former user

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

Jobs Per Server Per Hour (dynamic)

Picks up Scan Engine Server names automatically.

Code Block
sql
sql
declare @projectID int = 1
exec History.ScanServerProgressHourly @projectId

Image Added

Stored Procedure sql

Expand
titleHistory.ScanServerProgressHourly


Code Block
sql
sql
/*
-- 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
languagesql
titleHistory.ScanServerProgress
declare @projectID int = 1
exec History.ScanServerProgress  @projectId

Image Added

Stored Procedure sql

Expand
titleHistory.ScanServerProgress


Code Block
languagesql
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
sql
sql
declare @projectId int = 1
exec history.ServerThroughput @projectId

Image Added

Expand
titlehistory.ServerThroughput


Code Block
sql
sql
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.

...