It's a monitoring to check if it has any relevant jobs, tasks, database loading or business rules "stopped".
Currently, we are monitoring if it has any issues, but not if it's stopped.
Considering the company has a monitor to show up the monitoring from all the performance or issues, it will add to supervise the relevant process in real-time.
This is the development:
First I used Adobe Photoshop to work on the transparent red window, after that, I imported it to Blend
I've customized the XAML background to transparent, also, changed the location to Centralized:
xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="https://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="https://schemas.openxmlformats.org/markup-compatibility/2006"
WindowStartupLocation="CenterScreen" Top="0" Left="0"
xmlns:local="clr-namespace:RedFlashAlertWPF"
mc:Ignorable="d"
Title="MainWindow" Height="450" Width="800" AllowsTransparency="True" WindowStyle="None" HorizontalAlignment="Center" VerticalAlignment="Center" LostFocus="Window_LostFocus">
In Visual Studio, I've developed the C# code:
--// MainWindow.xaml.cs
===============================================================================
using System;
using System.Windows;
using System.Data.SqlClient;
using System.Threading;
using System.Timers;
using Timer = System.Timers.Timer;
using System.Windows.Threading;
namespace RedFlashAlertWPF
{
public partial class MainWindow : Window
{
private DispatcherTimer dispatcherTimer;
public MainWindow()
{
InitializeComponent();
txtAlert.Text = "No Alert Message";
// DispatcherTimer setup
dispatcherTimer = new System.Windows.Threading.DispatcherTimer();
dispatcherTimer.Tick += new EventHandler(dispatcherTimer_Tick);
dispatcherTimer.Interval = new TimeSpan(0, 0, 5);
dispatcherTimer.Start();
}
private void dispatcherTimer_Tick(object sender, EventArgs e)
{
ConnectionError:
SqlConnection cnnDB = new SqlConnection("Server=TG-SVR-SQL4;Database=DBA;Trusted_Connection=yes;");
try
{
cnnDB.Open();
}
catch (Exception ex)
{
goto ConnectionError;
}
string strAlert = "";
string sqlStr = "Select top 5 * from vwDBAWebAlert";
SqlCommand cmdSQL = new SqlCommand(sqlStr, cnnDB);
SqlDataReader rsSQL = cmdSQL.ExecuteReader();
while (rsSQL.Read())
{
strAlert += rsSQL["AlertDesc"].ToString() + Environment.NewLine + Environment.NewLine;
}
rsSQL.Close();
cnnDB.Close();
txtAlert.Text = strAlert;
if (strAlert == "")
{
this.WindowState = WindowState.Minimized;
}
else
{
this.WindowState = WindowState.Normal;
}
}
private void OnElapsedTime(object source, ElapsedEventArgs e)
{
ConnectionError:
SqlConnection cnnDB = new SqlConnection("Server=MyServer;Database=DBA;Trusted_Connection=yes;");
try
{
cnnDB.Open();
}
catch (Exception ex)
{
goto ConnectionError;
}
string strAlert = "";
string sqlStr = "Select top 5 * from vwDBAWebAlert";
SqlCommand cmdSQL = new SqlCommand(sqlStr, cnnDB);
SqlDataReader rsSQL = cmdSQL.ExecuteReader();
while (rsSQL.Read())
{
strAlert += rsSQL["AlertDesc"].ToString() + Environment.NewLine + Environment.NewLine;
}
rsSQL.Close();
cnnDB.Close();
txtAlert.Text = strAlert;
if (strAlert == "")
{
this.WindowState = WindowState.Minimized;
}
else
{
this.WindowState = WindowState.Maximized;
}
}
private void timerC(object state)
{
Environment.Exit(0);
}
private void Button_Click(object sender, RoutedEventArgs e)
{
this.WindowState = WindowState.Minimized;
}
}
}
--// I built in SQL a repository to save my Relevant Process
===============================================================================
USE [DBA]
GO
/****** Object: Table [dbo].[TDWSetting] Script Date: 10/12/2018 12:28:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Drop table [dbo].[RelevantProcess]
go
CREATE TABLE [dbo].[RelevantProcess](
[RelevantProcessID] [int] IDENTITY(1,1) NOT NULL,
[ProcessName] [varchar](150) NOT NULL,
[ProcessDescription] [varchar](250) NULL,
[LocalSource] [varchar](60) NULL,
[ProcessType] [varchar](50) NOT NULL,
[IsMonitoring] [char](1) NOT NULL,
[RelevanceLevel] [varchar](50) NOT NULL,
[Status] [varchar](50) NOT NULL,
[MustRuninMinute] [int] NULL,
[CriticalAfterLapsedinMinute] [int] NOT NULL,
[AlertAfterDatedinMinutes] AS ([CriticalAfterLapsedinMinute]-[MustRuninMinute]),
[MissingUpdatinginMinute] AS (datediff(minute,[LastSuccessFullExecution],getdate())),
[NextUpdatinginMinute] AS (datediff(minute,getdate(),dateadd(minute,[MustRuninMinute],[LastSuccessFullExecution]))),
[NextExecution] AS (dateadd(minute,[MustRuninMinute],[LastExecution])),
[AlertFlag] AS (case when datediff(minute,[LastSuccessFullExecution],getdate())>[CriticalAfterLapsedinMinute] AND [IsMonitoring]='Y' then (1) else (0) end),
[LastSuccessFullExecution] [datetime] NOT NULL,
[LastExecution] [datetime] NOT NULL,
[DataHash] AS (hashbytes('MD5',concat([ProcessName],[LocalSource]))) PERSISTED,
[CreatedOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
Create index IDX_RelevantProcessDataHash on [RelevantProcess]([DataHash])
go
--// This is an example how I can populate the Relevant Process table
===============================================================================
Use DBA
go
--======================================================================================================
--// DW - Product Movement - Loading
--======================================================================================================
Insert[dbo].[RelevantProcess](
[ProcessName],
[ProcessDescription],
[LocalSource],
[ProcessType], --// SQLJOB / TDW Loading / SQL Step
[IsMonitoring], --(Y)es or (N)o
[RelevanceLevel], --// High / Medium / Low
[MustRuninMinute],
[CriticalAfterLapsedinMinute],
[Status],
[LastSuccessFulExecution],
[LastExecution],
[CreatedOn]
)
Select
[ProcessName] = 'DW - Product Movement - Loading',
[ProcessDescription] = 'This process loads the Data from CRM Goods Movements to DW',
[LocalSource] = 'BIServer',
[ProcessType] = 'SQLJOB', --// SQLJOB / TDW Loading / SQL Step
[IsMonitoring] = 'Y', --(Y)es or (N)o
[RelevanceLevel] = 'Medium', --// High / Medium / Low
[MustRuninMinute] = 1440, --// 24 hours
[CriticalAfterLapsedinMinute] = 1470, -- 30 minutes after since the last time it ran,
[Status] = 'Never Monitored', --// Successuful / Disabled / Failed / Never Monitored
[LastSuccessFullExecution] = Getdate(),
[LastExecution] = Getdate(),
[CreatedOn] = Getdate()
GO
--======================================================================================================
--// DBA - LogShipping - Restore - Full Backup
--======================================================================================================
Insert[dbo].[RelevantProcess](
[ProcessName],
[ProcessDescription],
[LocalSource],
[ProcessType], --// SQLJOB / TDW Loading / SQL Step
[IsMonitoring], --(Y)es or (N)o
[RelevanceLevel], --// High / Medium / Low
[MustRuninMinute],
[CriticalAfterLapsedinMinute],
[Status],
[LastSuccessFulExecution],
[LastExecution],
[CreatedOn]
)
Select
[ProcessName] = 'DBA - LogShipping - Restore - Full Backup',
[ProcessDescription] = 'This process does the daily Full updating of reporting databases',
[LocalSource] = 'BIServer',
[ProcessType] = 'SQLJOB', --// SQLJOB / TDW Loading / SQL Step
[IsMonitoring] = 'Y', --(Y)es or (N)o
[RelevanceLevel] = 'High', --// High / Medium / Low
[MustRuninMinute] = 1440, -- 24 hours = 1440 minutes
[CriticalAfterLapsedinMinute] = 1470, -- 30 minutes after since the last time it ran
[Status] = 'Never Monitored', --// Successuful / Disabled / Failed / Never Monitored
[LastSuccessFullExecution] = Getdate(),
[LastExecution] = Getdate(),
[CreatedOn] = Getdate()
GO
--// This is an example reading the rules coming from DW loading
===============================================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE or ALTER Proc [dbo].[sp_DBAWEB_AddTDWLoadingRelevantProcess]
As
/*****************************************************************************************************************
Name: dbo.sp_DBAWEB_AddTDWLoadingRelevantProcess
Updates all the Relevant TDW Loading Status for Monitoring.
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
12 Dec 2018 Leandro Buffone Created Initial Version
*****************************************************************************************************************
Usage:
[sp_DBAWEB_AddTDWLoadingRelevantProcess]
*****************************************************************************************************************/
With BaseTDWLoading
As
(
SELECT
[Code]
,LastSuccessFullRunTime = [Value]
,LastExecution = [ModifiedOn]
,[Status] = Case When (DateDiff(day, value, modifiedOn) <= itMustUpdateinDays) Then 'Successful' Else 'Failed' End
FROM [TDW].[dbo].[Setting]
WHERE isActive = 'Yes'
and isDate(value) = 1
)
Update RP
Set [LastSuccessFullExecution] = JE.LastSuccessFullRunTime,
[LastExecution] = JE.LastExecution,
[Status] = JE.[Status]
From [dbo].[RelevantProcess] RP
Inner Join BaseTDWLoading JE on RP.[ProcessName] = JE.[Code]
Where
RP.ProcessType = 'TDWLoading'
--// This is an example reading from the SQL Jobs
===============================================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE or ALTER Proc [dbo].[sp_DBAWEB_AddSQLJobRelevantProcess]
As
/*****************************************************************************************************************
Name: dbo.sp_DBAWEB_UpdateSQLJobRelevantProcess
Updates all the Relevant SQL Job Status for Monitoring.
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
10 Dec 2018 Leandro Buffone Created Initial Version
*****************************************************************************************************************
Usage:
[sp_DBAWEB_AddSQLJobRelevantProcess]
*****************************************************************************************************************/
With BaseSQLJob
As
(
Select
[ServerName] = @@SERVERNAME
, [JobID] = h.job_id
, [JobName] = LTrim(RTrim(j.name))
, [JobCategory] = [cat].[name]
, [JobDescription] = j.description
, [LastRunDate] = h.run_date
, [LastRunTime] = msdb.dbo.agent_datetime(h.run_date,h.run_time)
, [LastSuccessFullRunTime] = IIF(h.run_status = 1, msdb.dbo.agent_datetime(h.run_date,h.run_time) , '1900-01-01 00:00:00.000')
, [JobStatusID] = h.run_status
, [JobStatus] = Case h.run_status
When 0 Then 'Failed'
When 1 Then 'Successful'
When 3 Then 'Cancelled'
When 4 Then 'In Progress'
Else 'Disabled'
End
,[OrderOfRun] = Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc)
From
msdb.dbo.sysjobs j
left Join msdb.dbo.sysjobhistory h On j.job_id = h.job_id and h.step_id=0 --only look @ Job Outcome step
Left join msdb.dbo.syscategories cat On j.category_id = cat.category_id
)
Update RP
Set [LastSuccessFullExecution] = JE.LastSuccessFullRunTime,
[LastExecution] = JE.LastRunTime,
[Status] = JE.JobStatus
From [dbo].[RelevantProcess] RP
Inner Join BaseSQLJob JE on RP.[ProcessName] = JE.[JobName]
Where
OrderOfRun = 1
and RP.ProcessType = 'SQLJOB'
--// This is the view I'm calling to check the last status
===============================================================================
use DBA
go
Create or alter View vwDBAWebAlert
as
SELECT
top 3
AlertDesc = Concat([ProcessName], ' in ', [LocalSource], ' (', [ProcessType], ')', char(13) , 'Last Execution: ', [LastSuccessFullExecution], ' Expected Execution at: ', [NextExecution]),
AlertToolTipDesc = Concat([ProcessName], ' (', [ProcessDescription] , ')')
FROM [DBA].[dbo].[RelevantProcess]
WHERE IsMonitoring = 'Y' and AlertFlag = 1
It's a monitoring to check if the relevant jobs are working and not "stopped".
Currently, we have monitoring to check if it has any issues, but not for any stopped processes that never should have stopped.
--// This is an example reading the rules coming from DW loading
===============================================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE or ALTER Proc [dbo].[sp_DBAWEB_AddTDWLoadingRelevantProcess]
As
/*****************************************************************************************************************
Name: dbo.sp_DBAWEB_AddTDWLoadingRelevantProcess
Updates all the Relevant TDW Loading Status for Monitoring.
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
12 Dec 2018 Leandro Buffone Created Initial Version
*****************************************************************************************************************
Usage:
[sp_DBAWEB_AddTDWLoadingRelevantProcess]
*****************************************************************************************************************/
With BaseTDWLoading
As
(
SELECT
[Code]
,LastSuccessFullRunTime = [Value]
,LastExecution = [ModifiedOn]
,[Status] = Case When (DateDiff(day, value, modifiedOn) <= itMustUpdateinDays) Then 'Successful' Else 'Failed' End
FROM [TDW].[dbo].[Setting]
WHERE isActive = 'Yes'
and isDate(value) = 1
)
Update RP
Set [LastSuccessFullExecution] = JE.LastSuccessFullRunTime,
[LastExecution] = JE.LastExecution,
[Status] = JE.[Status]
From [dbo].[RelevantProcess] RP
Inner Join BaseTDWLoading JE on RP.[ProcessName] = JE.[Code]
Where
RP.ProcessType = 'TDWLoading'
--// This is an example reading from the SQL Jobs
===============================================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE or ALTER Proc [dbo].[sp_DBAWEB_AddSQLJobRelevantProcess]
As
/*****************************************************************************************************************
Name: dbo.sp_DBAWEB_UpdateSQLJobRelevantProcess
Updates all the Relevant SQL Job Status for Monitoring.
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
10 Dec 2018 Leandro Buffone Created Initial Version
*****************************************************************************************************************
Usage:
[sp_DBAWEB_AddSQLJobRelevantProcess]
*****************************************************************************************************************/
With BaseSQLJob
As
(
Select
[ServerName] = @@SERVERNAME
, [JobID] = h.job_id
, [JobName] = LTrim(RTrim(j.name))
, [JobCategory] = [cat].[name]
, [JobDescription] = j.description
, [LastRunDate] = h.run_date
, [LastRunTime] = msdb.dbo.agent_datetime(h.run_date,h.run_time)
, [LastSuccessFullRunTime] = IIF(h.run_status = 1, msdb.dbo.agent_datetime(h.run_date,h.run_time) , '1900-01-01 00:00:00.000')
, [JobStatusID] = h.run_status
, [JobStatus] = Case h.run_status
When 0 Then 'Failed'
When 1 Then 'Successful'
When 3 Then 'Cancelled'
When 4 Then 'In Progress'
Else 'Disabled'
End
,[OrderOfRun] = Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc)
From
msdb.dbo.sysjobs j
left Join msdb.dbo.sysjobhistory h On j.job_id = h.job_id and h.step_id=0 --only look @ Job Outcome step
Left join msdb.dbo.syscategories cat On j.category_id = cat.category_id
)
Update RP
Set [LastSuccessFullExecution] = JE.LastSuccessFullRunTime,
[LastExecution] = JE.LastRunTime,
[Status] = JE.JobStatus
From [dbo].[RelevantProcess] RP
Inner Join BaseSQLJob JE on RP.[ProcessName] = JE.[JobName]
Where
OrderOfRun = 1
and RP.ProcessType = 'SQLJOB'
--// This is the view I'm calling to check the last status
===============================================================================
use DBA
go
Create or alter View vwDBAWebAlert
as
SELECT
top 3
AlertDesc = Concat([ProcessName], ' in ', [LocalSource], ' (', [ProcessType], ')', char(13) , 'Last Execution: ', [LastSuccessFullExecution], ' Expected Execution at: ', [NextExecution]),
AlertToolTipDesc = Concat([ProcessName], ' (', [ProcessDescription] , ')')
FROM [DBA].[dbo].[RelevantProcess]
WHERE IsMonitoring = 'Y' and AlertFlag = 1
It's a monitoring to check if the relevant jobs are working and not "stopped".
Currently, we have monitoring to check if it has any issues, but not for any stopped processes that never should have stopped.