MarkPryceMaherMSFT

SELECT * FROM (
select distinct eventtime,TRIM( replace(replace(replace(replace(
case when CHARINDEX('END TRY',table_details) > 3 then LEFT(table_details, CHARINDEX('END TRY',table_details)-4)
else table_details end, ''', N''',','),'N''',''),''';',''),'''','')) as bl_details
from (
select *, CHARINDEX('sys.fn_cdc_get_min_lsn(',SQLText) no_, 
SUBSTRING(SQLText,CHARINDEX('sys.fn_cdc_get_min_lsn(',SQLText) + len('sys.fn_cdc_get_min_lsn('),150) sss_
, 
case when CHARINDEX('sys.sp_cdc_help_change_data_capture',SQLText) > 0 then SUBSTRING(SQLText,CHARINDEX('sys.sp_cdc_help_change_data_capture',SQLText) + len('sys.sp_cdc_help_change_data_capture'),150)
when CHARINDEX('cdc_get_ddl_history',SQLText) > 0                      then SUBSTRING(SQLText,CHARINDEX('cdc_get_ddl_history @capture_instance = ',SQLText) + len('cdc_get_ddl_history @capture_instance = '),150)

MarkPryceMaherMSFT

SELECT
    X.event_xml.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    X.event_xml.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText,
    X.event_xml.value('(event/action[@name="database_name"]/value)[1]', 'varchar(128)') AS DatabaseName,
    X.event_xml.value('(event/action[@name="username"]/value)[1]', 'varchar(128)') AS UserName,
    X.event_xml.value('(event/@timestamp)[1]', 'datetime') AS EventTime
FROM sys.fn_xe_file_target_read_file('C:\XE\MonitorTableQueries*.xel', NULL, NULL, NULL) AS F
CROSS APPLY (SELECT CAST(F.event_data AS XML)) AS X(event_xml)
ORDER BY EventTime DESC;

MarkPryceMaherMSFT

-- Create Extended Events session
CREATE EVENT SESSION [MonitorTableQueries]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.username
    )

MarkPryceMaherMSFT

-- Stop the Extended Events session if it is running
IF EXISTS (
    SELECT 1
    FROM sys.server_event_sessions
    WHERE name = 'MonitorTableQueries'
)
BEGIN
    ALTER EVENT SESSION [MonitorTableQueries] ON SERVER STATE = STOP;
    PRINT 'MonitorTableQueries session stopped.';

MarkPryceMaherMSFT

-- Create Extended Events session
CREATE EVENT SESSION [MonitorTableQueries]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.username

MarkPryceMaherMSFT

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[help_change_feed]') AND type in (N'U')) DROP TABLE [dbo].[help_change_feed]

create table  help_change_feed 
(
	table_group_id uniqueidentifier ,
	table_group_name nvarchar(140) ,
	destination_location nvarchar(512) ,
	destination_credential nvarchar(247) ,
	destination_type nvarchar(247) ,
	workspace_id nvarchar(247) ,

MarkPryceMaherMSFT

%%pyspark

import sempy.fabric as fabric
import struct
import sqlalchemy
import pyodbc
import pandas as pd
from notebookutils import mssparkutils

#Function to Return sqlalchemt ODBC Engine, given a connection string and using Integrated AAD Auth to Fabric

MarkPryceMaherMSFT

@json('
"translator": {
	"type": "TabularTranslator",
	"mappings": [
		{
			"source": {
				"name": "t",
				"type": "DateTime",
				"physicalType": "datetime"
			},

MarkPryceMaherMSFT

import requests
from notebookutils import mssparkutils
import sempy.fabric as fabric 
from sempy.fabric.exceptions import FabricHTTPException, WorkspaceNotFoundException 
import json
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType
from datetime import datetime

# Get token

MarkPryceMaherMSFT

import pandas as pd
import struct
import sqlalchemy
import pyodbc
import notebookutils
import sempy.fabric as fabric
from sqlalchemy import text
import time

# -----------------------------------------------------------------------------