0

I have Azure Dashboard visualizing metrics of Azure SQL And Azure Data Factory. I have Azure Alert and LogicApps sending alert.

Now I would like to build monitoring report if table is empty or if there are data duplicates in Azure SQL. What is recommended too making query? What is recommended tool for displaying report for admin users of Azure solution? What is recommended way to send alert?

Kenny_I
  • 2,001
  • 5
  • 40
  • 94

1 Answers1

0

Keep it simple. Create sql job and just apply checks on it.

USE DATABASEname
select coalesce(count(*) ,0) as CHECKVALUE from dbo.table 
IF CHECKVALUE =0

BEGIN

EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'yourname@email.com',
  @body='tablename is empty......', 
  @subject ='tablename is empty',
  @profile_name ='yourname',
  @query =
    'USE DATABASEname
     (select coalesce(count(*) ,0) as CHECKVALUE from dbo.table )'

END
Lenroy Yeung
  • 291
  • 3
  • 8
  • Is it possible to execute sql job with Azure Dashboard or where query should be executed? – Kenny_I Oct 16 '21 at 12:08
  • If you want to appear it in your Azure dashboard then use Azure elastic database job instead. For your reference https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tsql-create-manage – Lenroy Yeung Oct 17 '21 at 01:43
  • How to add job to Azure Dashboard? I don't see in "Tile gallery" such option. – Kenny_I Oct 17 '21 at 13:50
  • You would need to trigger the record into Azure Table Storage and then go to Azure Dashboard > Markdown > select Insert content using url (paste your azure storage table url here) – Lenroy Yeung Oct 18 '21 at 04:54