Page Comparison

...

Code Block

	sql
	sql

declare @projectID int = 1
exec History.ScanServerProgressHourly @projectId

Stored Procedure sql

Expand

title	History.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

	sql
	sql

declare @projectID int = 1
select * from History.fn_ScanServerProgress ( @projectId )

Image Added

Function sql

Expand

title	Function sql code

Code Block

language	sql

create function History.fn_ScanServerProgress (@ProjectID int) 
returns @ScanServerProgress table(
     ProjectID          int
    ,ServerID           int
    ,server_name        nvarchar(255)
    ,servable_targets   int
    ,complete_targets   int
    ,complete_pct       numeric(6,2)
    primary key (ProjectID, ServerID)
    )
    WITH ENCRYPTION
as begin
    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]
        )

        insert into @ScanServerProgress
            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
    return
end
go

Job Data Summary:

Displays server info, current summary of the state of the jobs, and shows active jobs.

...

Versions Compared

Old Version 14

New Version 15

Key

Job Progress per Scan Server:

Job Data Summary: