SELECT distinct 'JobsandSteps' AS [Source], [sJSTP].[step_uid] AS [SourceId], 'SQL Server Job' AS [ProcessType], [sCAT].[name] AS [BusinessProcessSourceId], @@SERVERNAME as [ServerName], [sJOB].[name] AS [SQLServerJobSourceId],[sJSTP].[step_id] AS [StepSequence], [sJSTP].[step_name] AS [StepName], [sJSTP].[subsystem] AS [StepType],CASE WHEN [sJSTP].[subsystem] = 'SSIS' THEN REPLACE(LEFT(REPLACE([sJSTP].[command], '/SQL "', ''), CHARINDEX('"', REPLACE([sJSTP].[command], '/SQL "', ''), 1) -1), '\', '') END AS [SSISPackageSourceId], NULL AS [EmailSourceId], [sJSTP].[database_name] AS [DatabaseSourceId], [sJSTP].[command] AS [StepCommand], 0 AS [Duration]
--SELECT distinct [sJOB].[name] AS [SQLServerJobSourceId]
FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id] LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
WHERE [sJSTP].[command] like '%<text here>%'
