Hey, Kristof ! – Help for people that read …

Can you give me a master list for checking AI software for vulnerabilities ?

Post author By Kristof
Post date 2026-03-03

Sure. This is a snapshot of the MD I use to check AI-generated code for security risks. Before public builds, I ask my software agent to go through this list. Instructions what to do next for the agent are at the end of the document.

1. Input Validation & Injection Risks

Multi-Vector Injection: Have you checked for NoSQL, OS Command, and LDAP injections in addition to standard SQL injection?
Indirect Prompt Injection: If this agent processes external data (emails, web pages, user uploads), could that data contain hidden instructions that hijack the agent’s logic?
Trust Boundaries: Are you trusting any client-provided data (user IDs, roles, session states) that should instead be derived or verified strictly on the server?
Allow-Listing: Are all inputs validated against a strict ‘allow’ list (specifying expected types, lengths, and formats) rather than a ‘deny’ list?
Output Escaping: Does this code pass its output directly to another system (shell, browser, or database)? Treat your own generated output as untrusted user input and escape it accordingly.
Memory Safety (C/C++): If writing in C or C++, have you explicitly prevented buffer overflows, checked for null pointers, and ensured all allocated memory is freed in all execution paths?

2. Authentication, Authorization, & State

Direct Access & CSRF: What prevents a user from bypassing the UI? Have you implemented Anti-CSRF (Cross-Site Request Forgery) tokens for all state-changing actions?
Session Integrity: Are sessions configured with appropriate timeouts, “Secure” and “HttpOnly” flags, and protected against session fixation?
Fail-Secure Logic: Does the access control logic default to “Deny All” if it cannot reach its security configuration or if an internal error occurs?
Least Privilege: Is the application connecting to the database or external services with the absolute lowest level of privilege required for this specific task?
Excessive Agency: Does this code have more permission than it needs to function? Ensure it cannot perform unauthorized actions if a specific function is manipulated.

3. Sensitive Data & Cryptography

Secret Sprawl: Have you scanned this code for hardcoded secrets, including ‘temporary’ bypasses, API keys, or hardcoded credentials?
Modern Standards: Are you using industry-standard, modern cryptographic libraries (e.g., AES-GCM, Argon2) rather than deprecated methods like MD5 or SHA1?
No Plain-Text: Are passwords, tokens, or keys stored in plain-text or using reversible two-way encryption? They must be hashed or stored in a dedicated Secret Manager.
Logging Exposure: Does the error handling or logging logic record sensitive data, tokens, or PII (Personally Identifiable Information)?

4. Supply Chain & Environment

Verified Provenance: Are all third-party libraries/skills pulled from verified publishers? Are versions pinned to specific hashes to prevent “Dependency Confusion” attacks?
Safe Parsing: Does the code use safe parsers for data formats like YAML or JSON to prevent unsafe deserialization or “billion laughs” attacks?
AI Integrity: Are suggested AI models or “agentic skills” pulled from unverified sources? Verify the hashes of these integrations.

5. Privacy & Compliance (GDPR/EU Standards)

Data Minimization: Does this code only collect and process the absolute minimum personal data required for the task?
Redaction: Is sensitive PII redacted or anonymized before being sent to external logging or AI monitoring services?
Secure Headers: Are you enforcing secure HTTP headers (e.g., Content-Security-Policy, HSTS, X-Content-Type-Options)?

6. Advanced Probing & Red-Teaming

Risk Identification: Identify the top three security risks with this specific approach and describe a concrete attack scenario for each.
Scalability & DoS: What prevents a user from exhausting resources (CPU, Memory, DB Connections)? Is rate limiting or pagination implemented?
Concurrency: Are there any race conditions in this concurrent data access pattern that could lead to unauthorized state changes or data leakage?
The Adversarial Check: “If you were an attacker specifically trying to bypass the security logic you just wrote, what is the first thing you would try?”

7. Instructions for Agent

If any of the above checks return a “Fail” or “Uncertain,” flag the code block immediately and provide a remediation plan before proceeding. At no point should you change any code on your own. Report on the current situation and set up a roadmap listing each vulnerability or security risk on a scale of 1 to 5 with 1 being critical and 5 being advisory.

Do you know how to script database objects from a script?

Post author By Kristof
Post date 2024-07-22

Why yes? With this script it’s easy as ABC. Just fill in the list of items you want scripted and off you go. This way you can script any stored procedure, view, function or anything else that resides in the sys.sql_modules table.

There is a small piece of code that will loop through the definition if it’s longer than 4000 characters long. The PRINT statement will only print the first 4000 characters of a (n)varchar variable. To work around this, we split off 4000 characters, then find the last carriage return and throw away the remainder of the string. This way we don’t get any nasty splits on the screen. Then we print that string and we subtract the length of that string from the original string, and we go again.

DECLARE
    @FQObjectName NVARCHAR(257),
    @Definition   NVARCHAR(MAX),
    @Separator    NVARCHAR(80)  = REPLICATE('-', 80),
    @PrintBlock   NVARCHAR(4000)

DECLARE @tbDefinition TABLE (FQObjectName NVARCHAR(257), Definition NVARCHAR(MAX))

INSERT INTO
    @tbDefinition (FQObjectName, Definition)
SELECT
    CONCAT_WS('.', OBJECT_SCHEMA_NAME(SM.object_id), OBJECT_NAME(SM.object_id)) AS FQObjectName,
    SM.definition
FROM
    sys.sql_modules AS SM
WHERE
    OBJECT_NAME(SM.object_id) IN ('UnqualifiedObjectOne', 'UnqualifiedObjectOne')

DECLARE cDefinitions CURSOR FOR
SELECT  TD.FQObjectName, TD.Definition FROM @tbDefinition AS TD

OPEN cDefinitions

FETCH NEXT FROM cDefinitions
INTO
    @FQObjectName,
    @Definition

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Separator
    PRINT CONCAT('-- ', @FQObjectName)
    PRINT @Separator

    WHILE LEN(@Definition) > 4000
    BEGIN
        -- Fix because PRINT is limited to 4000 chars
        SET @PrintBlock = REVERSE(LEFT(@Definition, 4000))
        SET @PrintBlock = REVERSE(SUBSTRING(@PrintBlock, CHARINDEX(char(13), @PrintBlock), 99999))

        PRINT @PrintBlock
        SET @Definition = RIGHT(@Definition, LEN(@Definition) - LEN(@PrintBlock))
    END

    PRINT @Definition
    PRINT 'GO'
    FETCH NEXT FROM cDefinitions
    INTO
        @FQObjectName,
        @Definition
END

CLOSE cDefinitions
DEALLOCATE cDefinitions
GO

DECLARE

@FQObjectName NVARCHAR(257),

@Definition NVARCHAR(MAX),

@Separator NVARCHAR(80) = REPLICATE('-', 80),

@PrintBlock NVARCHAR(4000)

DECLARE @tbDefinition TABLE (FQObjectName NVARCHAR(257), Definition NVARCHAR(MAX))

INSERT INTO

@tbDefinition (FQObjectName, Definition)

SELECT

CONCAT_WS('.', OBJECT_SCHEMA_NAME(SM.object_id), OBJECT_NAME(SM.object_id)) AS FQObjectName,

SM.definition

FROM

sys.sql_modules AS SM

WHERE

OBJECT_NAME(SM.object_id) IN ('UnqualifiedObjectOne', 'UnqualifiedObjectOne')

DECLARE cDefinitions CURSOR FOR

SELECT TD.FQObjectName, TD.Definition FROM @tbDefinition AS TD

OPEN cDefinitions

FETCH NEXT FROM cDefinitions

INTO

@FQObjectName,

@Definition

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @Separator

PRINT CONCAT('-- ', @FQObjectName)

PRINT @Separator

WHILE LEN(@Definition) > 4000

BEGIN

-- Fix because PRINT is limited to 4000 chars

SET @PrintBlock = REVERSE(LEFT(@Definition, 4000))

SET @PrintBlock = REVERSE(SUBSTRING(@PrintBlock, CHARINDEX(char(13), @PrintBlock), 99999))

PRINT @PrintBlock

SET @Definition = RIGHT(@Definition, LEN(@Definition) - LEN(@PrintBlock))

END

PRINT @Definition

PRINT 'GO'

FETCH NEXT FROM cDefinitions

INTO

@FQObjectName,

@Definition

END

CLOSE cDefinitions

DEALLOCATE cDefinitions

Do you know how to check a phone number?

Post author By Kristof
Post date 2022-11-28

Of course I do! Here’s a little nifty function you can use or expand for your own needs. This was made for Dutch mobile phone numbers but can be easily tweaked for any other type of phone number.

create or alter function [dbo].[tvf_FormatPhoneNumber]
(
    @PhoneNumber nvarchar(max)
)
returns table
as
return
(
with LoopForDigits (number) as
(
    select
        substring(@PhoneNumber , NMB.number, 1) as [text()]
    from 
        master..spt_values as NMB
    where 
        substring(@PhoneNumber , NMB.number, 1) like '[0-9]'
    and NMB.number between 0 and len(@PhoneNumber )
    and NMB.type = 'P'
    for 
        xml path ('')
),
PhonePattern as
(
    select
        pattern = '%6[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
)
select
    PhoneNumber = concat('31', substring(DIG.number, patindex(PHP.pattern, DIG.number), 9))
from 
    LoopForDigits as DIG
        inner join 
    PhonePattern PHP
        on patindex(PHP.pattern, DIG.number) > 0
)

create or alter function [dbo].[tvf_FormatPhoneNumber]

(

@PhoneNumber nvarchar(max)

)

returns table

return

(

with LoopForDigits (number) as

(

select

substring(@PhoneNumber , NMB.number, 1) as [text()]

from

master..spt_values as NMB

where

substring(@PhoneNumber , NMB.number, 1) like '[0-9]'

and NMB.number between 0 and len(@PhoneNumber )

and NMB.type = 'P'

for

xml path ('')

PhonePattern as

(

select

pattern = '%6[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

)

select

PhoneNumber = concat('31', substring(DIG.number, patindex(PHP.pattern, DIG.number), 9))

from

LoopForDigits as DIG

inner join

PhonePattern PHP

on patindex(PHP.pattern, DIG.number) > 0

)

The way this works is as follows.

First we split the string into a table of single characters. We then filter the ones that aren’t numeric. We use the like function because isnumeric would not filter anything that is used to represent a number like ‘-‘ or ‘+’ in the phone numbers. Finally we concatenate with the xml-trick. Then, we select the part of the resulting string that matches our pattern.

Change the pattern to suit your country’s phone number style – or you can just do the check and return a boolean to indicate it’s actually a valid phone number or not.

How can I use JSON in T-SQL to maintain configurations

Post author By Kristof
Post date 2022-11-01

declare @JSON varchar(max);
set @JSON =
'
[
	{
		"ConfigurationParameterID":1,
		"ParameterCode":"Beehive",
		"ParameterDescription":"Global parameter",
		"ParameterDataType":"D",
		"DefaultValueChar":null,
		"DefaultValueNumeric":null,
		"DefaultValueDate":null,
		"DefaultValueBit":null,
		"CreationDate":"2021-07-14T00:00:00",
		"CreatedBy":"K. Elst",
		"LastUpdateDate":"2021-07-14T00:00:00",
		"LastUpdatedBy":"K. Elst"
	},
	{
		"ConfigurationParameterID":2,
		"ParameterCode":"Ape",
		"ParameterDescription":"It''s a bee",
		"ParameterDataType":"C",
		"DefaultValueChar":null,
		"DefaultValueNumeric":null,
		"DefaultValueDate":null,
		"DefaultValueBit":null,
		"CreationDate":"2021-07-15T00:00:00",
		"CreatedBy":"K. Elst",
		"LastUpdateDate":"2021-07-15T00:00:00",
		"LastUpdatedBy":"K. Elst"
	}
]';

select
    PRM.ConfigurationParameterID,
    PRM.ParameterCode,
    PRM.ParameterDescription,
    PRM.ParameterDataType,
    PRM.DefaultValueChar,
    PRM.DefaultValueNumeric,
    PRM.DefaultValueDate,
    PRM.DefaultValueBit,
    PRM.CreationDate,
    PRM.CreatedBy,
    PRM.LastUpdateDate,
    PRM.LastUpdatedBy
from openjson(@JSON)
with
(
    ConfigurationParameterID int,
    ParameterCode nvarchar(50),
    ParameterDescription nvarchar(255),
    ParameterDataType nchar(1),
    DefaultValueChar nvarchar(max),
    DefaultValueNumeric float,
    DefaultValueDate datetime,
    DefaultValueBit bit,
    CreationDate datetime,
    CreatedBy nvarchar(255),
    LastUpdateDate datetime,
    LastUpdatedBy nvarchar(255)
)
as PRM

declare @JSON varchar(max);

set @JSON =

[

{

"ConfigurationParameterID":1,

"ParameterCode":"Beehive",

"ParameterDescription":"Global parameter",

"ParameterDataType":"D",

"DefaultValueChar":null,

"DefaultValueNumeric":null,

"DefaultValueDate":null,

"DefaultValueBit":null,

"CreationDate":"2021-07-14T00:00:00",

"CreatedBy":"K. Elst",

"LastUpdateDate":"2021-07-14T00:00:00",

"LastUpdatedBy":"K. Elst"

{

"ConfigurationParameterID":2,

"ParameterCode":"Ape",

"ParameterDescription":"It''s a bee",

"ParameterDataType":"C",

"DefaultValueChar":null,

"DefaultValueNumeric":null,

"DefaultValueDate":null,

"DefaultValueBit":null,

"CreationDate":"2021-07-15T00:00:00",

"CreatedBy":"K. Elst",

"LastUpdateDate":"2021-07-15T00:00:00",

"LastUpdatedBy":"K. Elst"

}

]';

select

PRM.ConfigurationParameterID,

PRM.ParameterCode,

PRM.ParameterDescription,

PRM.ParameterDataType,

PRM.DefaultValueChar,

PRM.DefaultValueNumeric,

PRM.DefaultValueDate,

PRM.DefaultValueBit,

PRM.CreationDate,

PRM.CreatedBy,

PRM.LastUpdateDate,

PRM.LastUpdatedBy

from openjson(@JSON)

with

(

ConfigurationParameterID int,

ParameterCode nvarchar(50),

ParameterDescription nvarchar(255),

ParameterDataType nchar(1),

DefaultValueChar nvarchar(max),

DefaultValueNumeric float,

DefaultValueDate datetime,

DefaultValueBit bit,

CreationDate datetime,

CreatedBy nvarchar(255),

LastUpdateDate datetime,

LastUpdatedBy nvarchar(255)

)

as PRM

Do you know how I can easily perform an 11-test (Elfproef)

Post author By Kristof
Post date 2022-09-06

The elfproef is a Dutch validation check that was used for bank account numbers before the introduction of IBAN and is still used for BSN (National identifier) and payment references in bank transaction. Here’s a sweet inline table valued function that will yield instant results. 1 million iterations of this take up almost no cpu time so it’s very fast.

The first part checks if the supplied value is numeric. If it is , we work with the supplied value, otherwise we work with the value 1 which will never yield a valid BSN. We cannot work with if/then constructs because we are working with an inline table function.

I used a JSON array for configuration because it makes the code easier to read. If you want to know how an elfproef works, here’s an explanation in Dutch: https://nl.wikipedia.org/wiki/Elfproef

Example usage: select BSN from Personen as PRS WHERE dbo.tvf_CheckElfProef(PRS.BSN) = 0

create or alter function dbo.tvf_CheckElfproef
(
    @Bsn varchar(9)
)
returns table
as
return
(
with Bsn as
(
    select
        Nr = cast(iif(isnumeric(@Bsn) = 1, @BsnNr, 1) as bigint)
),
Factoren as
(
    select
        Positie
      , Factor
    from openjson('
        [
            {"Positie": 1,"Factor": -1},
            {"Positie": 2,"Factor": 2},
            {"Positie": 3,"Factor": 3},
            {"Positie": 4,"Factor": 4},
            {"Positie": 5,"Factor": 5},
            {"Positie": 6,"Factor": 6},
            {"Positie": 7,"Factor": 7},
            {"Positie": 8,"Factor": 8},
            {"Positie": 9,"Factor": 9}
        ]
        ') with (Positie smallint, Factor smallint)
),
Totalen as
(
    select
        Totaal = sum((nr % power(10, Positie) / power(10, Positie - 1)) * Factor)
    from Factoren
    cross join Bsn
)
select
    ResultaatElfProef = Totaal % 11
from Totalen
)

create or alter function dbo.tvf_CheckElfproef

(

@Bsn varchar(9)

)

returns table

return

(

with Bsn as

(

select

Nr = cast(iif(isnumeric(@Bsn) = 1, @BsnNr, 1) as bigint)

Factoren as

(

select

Positie

, Factor

from openjson('

[

{"Positie": 1,"Factor": -1},

{"Positie": 2,"Factor": 2},

{"Positie": 3,"Factor": 3},

{"Positie": 4,"Factor": 4},

{"Positie": 5,"Factor": 5},

{"Positie": 6,"Factor": 6},

{"Positie": 7,"Factor": 7},

{"Positie": 8,"Factor": 8},

{"Positie": 9,"Factor": 9}

]

') with (Positie smallint, Factor smallint)

Totalen as

(

select

Totaal = sum((nr % power(10, Positie) / power(10, Positie - 1)) * Factor)

from Factoren

cross join Bsn

)

select

ResultaatElfProef = Totaal % 11

from Totalen

)

Do you know how to fix error 502 or 504 when saving a game in Baldur’s Gate 3?

Post author By Kristof
Post date 2022-06-13

Actually, I do. You have too many save games and you need to delete a few. But, there’s no delete option I hear you say? Simply select a save game and hit “delete” on the keyboard. It will ask you if you are sure (of course you are) and then you can save again. Woohoo! That’s some hard earned XP saved right there.

I need a script that will search all objects in a database for text or a combination of keywords

Post author By Kristof
Post date 2022-03-16

Oh, and I need a way to present it to my boss in an easy-to-use-excel-format.

Well, this code will search all your databases for the text combinations you provide and return the views that contain the keywords. You can easily modify this to include stored procedures or functions and you can extend the search combinations and what it says as a response to certain keywords being found.

declare @SearchText       nvarchar(max) = 'MuFunction(@SomeParam)'
      , @IncludeSystemDbs as tinyint    = 0 --1 to include

declare @tbSearchResults table (
    fqo                     nvarchar(384)
  , object_type_description nvarchar(max)
)
declare @SearchSql nvarchar(max) = formatmessage(
'
use ?

select 
    concat_ws(''.'', quotename(''?''), quotename(object_schema_name(ASM.object_id)), quotename(object_name(ASM.object_id))),
    OBJ.type_desc
from 
    sys.all_sql_modules as ASM
        inner join 
    sys.objects OBJ on ASM.object_id = OBJ.object_id
where 
    ASM.definition like ''%%%s%%''
and exists (
                select 
                    1/0
                from
                    sys.databases as DTB
                where
                (
                    DTB.name = ''?''
                and (
                        is_distributor = 0
                    or  ''?'' not in (''master'',''tempdb'',''model'', ''msdb'')
                    or %d = 1
                    )
                )
            )
', @SearchText, @IncludeSystemDbs)

insert into @tbSearchResults
exec sys.sp_MSforeachdb @command1 = @SearchSql

select
    SRS.object_type_description
  , SRS.fqo
from @tbSearchResults as SRS
order by 1
       , 2

declare @SearchText nvarchar(max) = 'MuFunction(@SomeParam)'

, @IncludeSystemDbs as tinyint = 0 --1 to include

declare @tbSearchResults table (

fqo nvarchar(384)

, object_type_description nvarchar(max)

)

declare @SearchSql nvarchar(max) = formatmessage(

use ?

select

concat_ws(''.'', quotename(''?''), quotename(object_schema_name(ASM.object_id)), quotename(object_name(ASM.object_id))),

OBJ.type_desc

from

sys.all_sql_modules as ASM

inner join

sys.objects OBJ on ASM.object_id = OBJ.object_id

where

ASM.definition like ''%%%s%%''

and exists (

select

1/0

from

sys.databases as DTB

where

(

DTB.name = ''?''

and (

is_distributor = 0

or ''?'' not in (''master'',''tempdb'',''model'', ''msdb'')

or %d = 1

)

', @SearchText, @IncludeSystemDbs)

insert into @tbSearchResults

exec sys.sp_MSforeachdb @command1 = @SearchSql

select

SRS.object_type_description

, SRS.fqo

from @tbSearchResults as SRS

order by 1

, 2

Do you know how to fix the NFC and wireless charging not working on my Google Pixel4?

Post author By Kristof
Post date 2022-03-02

Recently, the wireless charging on my Google Pixel4 stopped working. I blamed the wireless charging pad but it wasn’t until I travelled back to Europe and wanted to use my wireless payments that I noticed the NFC was also not working.

It now turns out it’s due to the back of the phone being slightly detached from the rest of the phone. It’s hardly noticeable, especially since I always keep it in a silicone case but removing the phone from the case revealed the right side (when the phone screen is away from you) to have a slight gap that I can run my nail through. Pressing down on the back immediately fixed the problem and at least the wireless charging is working once again. I hope adjusting the case will hold the back down hard enough. Other reports have users opening the phone, bending the pins and then trying to glue the back to the phone … for now, things are working again by just pushing it back down. Otherwise I will try duct tape.

I need a dashboard for my SQL Agent JOBS that run SSIS Packages

Post author By Kristof
Post date 2021-09-22

I hear you. Here is a script in T-SQL that will fetch all the jobs (including the ones that are currently running) and displays a dashboard for each step. It will also display the last message SSIS generated (last error message in case of failure, or last message in case of a successful run).

WITH
	LastSessionId
AS
	(
		SELECT
			session_id	= MAX(session_id), job_id
		FROM
			msdb.dbo.sysjobactivity
		GROUP BY
			job_id
	),
	LastSessionInformation
AS
	(
		SELECT
			sja.session_id,
			sja.job_id,
			sja.job_history_id,
			sja.start_execution_date,
			sja.last_executed_step_date,
			sja.stop_execution_date,
			sja.next_scheduled_run_date
		FROM
			msdb.dbo.sysjobactivity AS sja
			INNER JOIN
			LastSessionId AS lsi
			ON sja.job_id = lsi.job_id
			AND sja.session_id = lsi.session_id
	),
	SsisExecution
AS
	(
		SELECT
			exe.folder_name,
			exe.project_name,
			exe.package_name,
			exe.process_id,
			opr.status,
			opr.operation_id,
			exe.start_time,
			exe.execution_id
		FROM
			SSISDB.catalog.operations AS opr
			INNER JOIN
			SSISDB.catalog.executions AS exe
			ON opr.process_id = exe.process_id
			AND opr.status IN
					(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded
		WHERE
			opr.object_type = 20	-- package
	),
	LatestMessage
AS
	(
		SELECT
			last_message_id = MAX(msg.event_message_id), msg.message_type, msg.operation_id
		FROM
			SSISDB.catalog.event_messages AS msg
		GROUP BY
			msg.operation_id, msg.message_type
	)
SELECT
	job.job_id,
	sjh.instance_id,
	job = job.name,
	lsi.start_execution_date,
	last_executed_date = COALESCE(lsi.stop_execution_date, lsi.last_executed_step_date),
	lsi.next_scheduled_run_date,
	sjh.step_id,
	sjh.step_name,
	sjh.run_status,
	IIF(lsi.start_execution_date IS NOT NULL, COALESCE(sjh.message, '>>> RUNNING'), NULL),
	package = exe.folder_name + '\' + exe.project_name + '\' + exe.package_name,
	msg.message,
	msg.message_source_name,
	msg.execution_path,
	msg.package_path,
	details = 'SELECT * FROM SSISDB.catalog.event_messages WHERE operation_id = '
			+ CAST(exe.operation_id AS VARCHAR(50)) + ' ORDER BY message_time DESC'
FROM
	msdb.dbo.sysjobs AS job
	INNER JOIN
	LastSessionInformation AS lsi
	ON lsi.job_id = job.job_id
	LEFT JOIN
	msdb.dbo.sysjobhistory AS sjh
	ON sjh.job_id = job.job_id
	AND (
			(	lsi.job_history_id >= sjh.instance_id
			AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)
			AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)
			)
		OR	(	lsi.job_history_id IS NULL
			AND	sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)
			AND	sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)
			)
		)
	LEFT JOIN
	msdb.dbo.sysjobsteps AS sjs
	ON sjs.job_id = job.job_id
	AND sjs.step_id = sjh.step_id
	OUTER APPLY
	(
		SELECT
			ExecutionId = SUBSTRING(
									sjh.message,
									CHARINDEX('Execution ID: ', sjh.message) + 14,
									CHARINDEX('.', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)
									- CHARINDEX('Execution ID: ', sjh.message) - 14
								)
		WHERE
			sjs.subsystem = 'ssis'
		AND CHARINDEX('Execution ID: ', sjh.message) > 0
		AND CHARINDEX('Failed', sjh.message) = 0
		UNION ALL
		SELECT
			ExecutionId = SUBSTRING(
									sjh.message,
									CHARINDEX('Execution ID: ', sjh.message) + 14,
									CHARINDEX(',', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)
									- CHARINDEX('Execution ID: ', sjh.message) - 14
								)
		WHERE
			sjs.subsystem = 'ssis'
		AND CHARINDEX('Execution ID: ', sjh.message) > 0
		AND CHARINDEX('Failed', sjh.message) > 0
	) AS eii
	LEFT JOIN
	SsisExecution AS exe
	ON sjs.subsystem = 'SSIS'
	AND exe.execution_id = CAST(eii.ExecutionId AS INT)
	LEFT JOIN
	LatestMessage AS lem
	ON lem.operation_id = exe.operation_id
	AND lem.message_type = 120
	LEFT JOIN
	LatestMessage AS lmg
	ON lmg.operation_id = exe.operation_id
	AND lmg.message_type = 40
	LEFT JOIN
	SSISDB.catalog.event_messages AS msg
	ON msg.event_message_id = COALESCE(lem.last_message_id, lmg.last_message_id)
WHERE
	job.name LIKE 'hds%'
ORDER BY
	lsi.start_execution_date DESC, job.name, instance_id DESC

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

WITH

LastSessionId

(

SELECT

session_id = MAX(session_id), job_id

FROM

msdb.dbo.sysjobactivity

GROUP BY

job_id

LastSessionInformation

(

SELECT

sja.session_id,

sja.job_id,

sja.job_history_id,

sja.start_execution_date,

sja.last_executed_step_date,

sja.stop_execution_date,

sja.next_scheduled_run_date

FROM

msdb.dbo.sysjobactivity AS sja

INNER JOIN

LastSessionId AS lsi

ON sja.job_id = lsi.job_id

AND sja.session_id = lsi.session_id

SsisExecution

(

SELECT

exe.folder_name,

exe.project_name,

exe.package_name,

exe.process_id,

opr.status,

opr.operation_id,

exe.start_time,

exe.execution_id

FROM

SSISDB.catalog.operations AS opr

INNER JOIN

SSISDB.catalog.executions AS exe

ON opr.process_id = exe.process_id

AND opr.status IN

(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded

WHERE

opr.object_type = 20 -- package

LatestMessage

(

SELECT

last_message_id = MAX(msg.event_message_id), msg.message_type, msg.operation_id

FROM

SSISDB.catalog.event_messages AS msg

GROUP BY

msg.operation_id, msg.message_type

)

SELECT

job.job_id,

sjh.instance_id,

job = job.name,

lsi.start_execution_date,

last_executed_date = COALESCE(lsi.stop_execution_date, lsi.last_executed_step_date),

lsi.next_scheduled_run_date,

sjh.step_id,

sjh.step_name,

sjh.run_status,

IIF(lsi.start_execution_date IS NOT NULL, COALESCE(sjh.message, '>>> RUNNING'), NULL),

package = exe.folder_name + '\' + exe.project_name + '\' + exe.package_name,

msg.message,

msg.message_source_name,

msg.execution_path,

msg.package_path,

details = 'SELECT * FROM SSISDB.catalog.event_messages WHERE operation_id = '

+ CAST(exe.operation_id AS VARCHAR(50)) + ' ORDER BY message_time DESC'

FROM

msdb.dbo.sysjobs AS job

INNER JOIN

LastSessionInformation AS lsi

ON lsi.job_id = job.job_id

LEFT JOIN

msdb.dbo.sysjobhistory AS sjh

ON sjh.job_id = job.job_id

AND (

( lsi.job_history_id >= sjh.instance_id

AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)

AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)

)

OR ( lsi.job_history_id IS NULL

AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)

AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)

)

LEFT JOIN

msdb.dbo.sysjobsteps AS sjs

ON sjs.job_id = job.job_id

AND sjs.step_id = sjh.step_id

OUTER APPLY

(

SELECT

ExecutionId = SUBSTRING(

sjh.message,

CHARINDEX('Execution ID: ', sjh.message) + 14,

CHARINDEX('.', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)

- CHARINDEX('Execution ID: ', sjh.message) - 14

)

WHERE

sjs.subsystem = 'ssis'

AND CHARINDEX('Execution ID: ', sjh.message) > 0

AND CHARINDEX('Failed', sjh.message) = 0

UNION ALL

SELECT

ExecutionId = SUBSTRING(

sjh.message,

CHARINDEX('Execution ID: ', sjh.message) + 14,

CHARINDEX(',', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)

- CHARINDEX('Execution ID: ', sjh.message) - 14

)

WHERE

sjs.subsystem = 'ssis'

AND CHARINDEX('Execution ID: ', sjh.message) > 0

AND CHARINDEX('Failed', sjh.message) > 0

) AS eii

LEFT JOIN

SsisExecution AS exe

ON sjs.subsystem = 'SSIS'

AND exe.execution_id = CAST(eii.ExecutionId AS INT)

LEFT JOIN

LatestMessage AS lem

ON lem.operation_id = exe.operation_id

AND lem.message_type = 120

LEFT JOIN

LatestMessage AS lmg

ON lmg.operation_id = exe.operation_id

AND lmg.message_type = 40

LEFT JOIN

SSISDB.catalog.event_messages AS msg

ON msg.event_message_id = COALESCE(lem.last_message_id, lmg.last_message_id)

WHERE

job.name LIKE 'hds%'

ORDER BY

lsi.start_execution_date DESC, job.name, instance_id DESC

How do I solve “illegal characters in path” in my Octopus deploy?

Post author By Kristof
Post date 2021-09-08

You won’t believe this, but the reason why I got this was a weird trailing backslash. I failed to find where it came from and after HOURS of searching, I found out it was because I had entered a variable in the process step and accidentally type ENTER before the variable. So it was essentially a CRLF followed by the variable name. For some reason, that caused the illegal character. Guess it was trying to insert the CRLF in the path … Well, check your variable listings in your steps to see if you have any leading or trailing enters …