SQL Server Agent log integration

  1. Create a view in the admin SQL Server database (described in the Creating a centralized IT monitoring solution with Power BI recipe earlier in this chapter) that queries the dbo.sysjobhistory and dbo.sysjobs tables in the msdb database:
CREATE VIEW BI.vFact_AgentJobHistory AS SELECT [h].[server] as [Server], [j].[name] AS [Job Name], CASE [j].[enabled] WHEN 0 THEN 'Disabled' WHEN 1 THEN 'Enabled' END AS [Job Status], [j].[date_created] as [Date Created], [j].[date_modified] as [Date Modified] , [j].[description] as [Job Description], [h].[step_id] AS [Step ID], [h].[step_name] AS [Step Name], CAST(STR([h].[run_date],8, 0) AS date) AS [Run Date], CAST(STUFF(STUFF(RIGHT('000000' + CAST ( [h].[run_time] AS ...

Get Microsoft Power BI Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.