Monthly SQL Server Agent Jobs report

  • Amit Jethva

    SSCarpal Tunnel

    Points: 4707

    Comments posted to this topic are about the item Monthly SQL Server Agent Jobs report


    -- Amit



    "There is no 'patch' for stupidity."



    Download the Updated SQL Server 2005 Books Online.

  • sachin-375765

    Newbie

    Points: 9

    Thanks for the query. Do you have the new version where you have taken care of jobs that run twice or more in a day.

    Sachin

  • The Danish Dynamo

    SSC-Addicted

    Points: 464

    Here is a version of your nice script that uses CTE to avoid temp table. It's also re-formatted with SQL Prompt.

    DECLARE @year int = 2015

    DECLARE @month tinyint = 8

    ;WITH jobs

    AS ( SELECT j.name AS [JobName] ,

    SUBSTRING(CONVERT(varchar, run_date), 7, 2) AS [Day] ,

    MAX(CASE run_status

    WHEN 1 THEN 'S'

    WHEN 0 THEN 'F'

    WHEN 2 THEN 'R'

    WHEN 3 THEN 'C'

    ELSE 'P'

    END) AS [Status]

    FROM msdb..sysjobhistory h ,

    msdb..sysjobs j

    WHERE j.enabled = 1

    AND j.job_id = h.job_id

    AND run_date BETWEEN ( ( @year * 10000 ) + ( @month * 100 )

    + 1 )

    AND ( ( @year * 10000 ) + ( @month * 100 )

    + 32 )

    AND h.step_id = 0

    GROUP BY j.name ,

    SUBSTRING(CONVERT(varchar, run_date), 7, 2)

    )

    SELECT

    JobName ,

    max(case Day when '01' then Status else '' end ) As [01],

    max(case Day when '02' then Status else '' end ) As [02],

    max(case Day when '03' then Status else '' end ) As [03],

    max(case Day when '04' then Status else '' end ) As [04],

    max(case Day when '05' then Status else '' end ) As [05],

    max(case Day when '06' then Status else '' end ) As [06],

    max(case Day when '07' then Status else '' end ) As [07],

    max(case Day when '08' then Status else '' end ) As [08],

    max(case Day when '09' then Status else '' end ) As [09],

    max(case Day when '10' then Status else '' end ) As [10],

    max(case Day when '11' then Status else '' end ) As [11],

    max(case Day when '12' then Status else '' end ) As [12],

    max(case Day when '13' then Status else '' end ) As [13],

    max(case Day when '14' then Status else '' end ) As [14],

    max(case Day when '15' then Status else '' end ) As [15],

    max(case Day when '16' then Status else '' end ) As [16],

    max(case Day when '17' then Status else '' end ) As [17],

    max(case Day when '18' then Status else '' end ) As [18],

    max(case Day when '19' then Status else '' end ) As [19],

    max(case Day when '20' then Status else '' end ) As [20],

    max(case Day when '21' then Status else '' end ) As [21],

    max(case Day when '22' then Status else '' end ) As [22],

    max(case Day when '23' then Status else '' end ) As [23],

    max(case Day when '24' then Status else '' end ) As [24],

    max(case Day when '25' then Status else '' end ) As [25],

    max(case Day when '26' then Status else '' end ) As [26],

    max(case Day when '27' then Status else '' end ) As [27],

    max(case Day when '28' then Status else '' end ) As [28],

    max(case Day when '29' then Status else '' end ) As [29],

    max(case Day when '30' then Status else '' end ) As [30],

    max(case Day when '31' then Status else '' end ) As [31]

    FROM jobs

    GROUP BY JobName

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply