December 18, 2015 at 11:11 am
Hello Experts,
I am trying to pull a user name in the below text message using T-SQL. I am really close but some how its not stripping the user name correctly.
Any guidance please.
Message Column Data: The job succeeded. The Job was invoked by User DOMAIN\USER1. The last step to run was step 1 (TestJob).
Below is my sql query I am using to pull DOMAIN\USER1
SELECT USERNAME=SUBSTRING(ltrim(rtrim(jh.message)),
CHARINDEX('was invoked by user', ltrim(rtrim(jh.message))) + LEN('was invoked by user')+1
, CHARINDEX('. The last step',ltrim(rtrim(jh.message)))
-CHARINDEX('was invoked by user', ltrim(rtrim(jh.message)))- LEN('was invoked by user')-1) FROM dbo.sysjobhistory jh
December 18, 2015 at 12:01 pm
Some how in the script CHARINDEX('. The last step',ltrim(rtrim(jh.message))) was not returning exact value as 63 but was giving 0. I fixed this by removing '.' in the above script and was able to parse the user name correctly.
SELECT USERNAME=
SUBSTRING(LTRIM(RTRIM(Hist.message)),
CHARINDEX('was invoked by User ',ltrim(rtrim(Hist.message)))+LEN('was invoked by User ')
,CHARINDEX(' The last step',ltrim(rtrim(Hist.message)))-CHARINDEX('was invoked by User ',ltrim(rtrim(Hist.message)))
-Len('was invoked by User')-2)
FROM sysjobs JOB
INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id
WHERE JOB.name = '??jobname??' and Hist.step_id=0
ORDER BY HIST.run_date, HIST.run_time
December 18, 2015 at 2:26 pm
This might be simpler.
SELECT LEFT( trunc.message, CHARINDEX(' ', trunc.message)), *
FROM dbo.sysjobhistory jh
CROSS APPLY( SELECT SUBSTRING( message, 49, 4000)) trunc(message)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply