1 Reply Latest reply on Sep 8, 2017 12:09 PM by njoylif

    SQL Server User Experience Monitor (SUEM) - Custom SQL Query

    brandonr

      Hello Thwack!

      I'm trying to run a custom SQL query against a database and get back a set of information to be displayed.  I've been attempting to use the SQL SUEM, but I'm running into an issue.

      When I run the query, I only seem to get one cell of information back instead of the information from the entire scripts.

       

      Here is the expected outcome:

      SQL Script Expected Outcome

       

      After inputting the (adjusted) code into SUEM, here is the result I get:

      SQL SUEM Test

       

      As you can see, I'm only getting the first data cell instead of the information from the entire scripts.

      I've read through a couple other posts about this and I've added a "SELECT 0" to my script to get it to even run, but I'm trying to figure out how to get the entire script to run/output properly.

      Here is the adjusted and sanitized scripts below:

       

      use msdb

      SELECT 0, j.name JobName,h.step_name StepName,

           CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,

           STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,

           STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS run_duration,

           case h.run_status when 0 then 'failed'

           when 1 then 'Succeded'

           when 2 then 'Retry'

           when 3 then 'Cancelled'

           when 4 then 'In Progress'

           else 'unknown'

           end as ExecutionStatus

           FROM [SERVERNAME].msdb.dbo.sysjobhistory h left join msdb.dbo.sysjobs j

           ON j.job_id = h.job_id

           where name in ('KNX Processor: F6P430_F6P_ALL_XR_BACKUP'

           ,'KNX Processor: F6P430_F6P_PREMRP_MD'

           ,'KNX Processor: F6P430_F6P_PREMRP_TD1_INIT'

           ,'KNX Processor: F6P430_F6P_PREMRP_TD2_INIT'

           ,'KNX Processor: F6P430_F6P_PREMRP_TD3_INIT'

           ,'KNX Processor: F6P430_F6P_2ND_RUN_NC'

           ,'KNX Processor: F6P430_F6_XR_OPT_BACKUP')

           and CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME) > getdate()-10

           and h.step_name = '(Job outcome)'

           and h.run_status <> 4

       

       

      Any help would be greatly appreciated!

        • Re: SQL Server User Experience Monitor (SUEM) - Custom SQL Query
          njoylif

          ayup, the end user experience returns value for one cell only.

          one workaround is to end your query with FOR XML PATH()

          see THIS post where this example is given:

          DATE / MSG of Trap(s) in last 5 minutes:

          ------------------------------------------------------

          ${SQL:select CAST(t.DateTime as DateTime2(0)) as DateTime, ' --- '+tvb.OIDValue +CHAR(13)+CHAR(10) as Value FROM Nodes WITH(NOLOCK) JOIN Traps t WITH(NOLOCK) ON (Nodes.NodeID=t.NodeID and t.Tag like '%CriticalSW%' and t.DateTime between DATEADD(MI,-6,getdate()) and GETDATE()  ) JOIN TrapVarbinds tvb WITH(NOLOCK) ON (t.TrapID=tvb.TrapID) Order By t.DateTime FOR XML PATH('')}

           

          if you want to get fancy with formatting you can nest and use the () to add rows, etc...

          I use this in a few of my email alerts, though the "quote"ing gets tricky