Show user friendly job_name in top level column program_name instead of job_id in hex

[mfuller333]

Just wondering the possibility of returning the job name in the top level column [program_name] instead of its [job_id] in hex?

At the top level its nice to see at a glance what jobs are colliding without having to dig down into the additional_info XML. I find myself toggling back and forth often which can get cumbersome when there are a lot of jobs involved in a block chain.

[amachanic]

I didn't want to do this back when I implemented name resolution as part of @get_additional_info, but TBH I don't remember why. I'm happy to take another look at this point and see if there's some good way to make it happen. One important note is that I think I'd probably still only do it if @get_additional_info is enabled, since it's not free.

[mfuller333]

That makes sense, I agree, only if @get_additional_info is enabled, no additional cost otherwise.

[erikdarlingdata]

I have a feeling this isn't going to work in Azure SQL DB, e.g. #72 unless we do some more dynamic checks. I'll wait to see if @amachanic can recall why it was avoided in the first place before spending time on it, though.

[amachanic]

It will work fine. I've already got it mostly sorted; just trying to decide on a format.

@mfuller333 Do you have examples of what the program name looks like for non-T-SQL steps? I just noticed that I have it hardcoded to only handle those: https://github.com/amachanic/sp_whoisactive/blob/master/sp_WhoIsActive.sql#L2791

[amachanic]

After looking at a few examples, I think we need a bit more research here. We can do this for just T-SQL but ideally I'd like to solve for some other cases as well.

Right now here's what I have for example program_name formats:

• T-SQL: "SQLAgent - TSQL JobStep (Job 0x2626E3FEEF8EEC4B9A871096FE360F7D : Step 1)" (0x... == Binary-formatted GUID, relates to msdb.dbo.sysjobs, job_id column; Step X == ID from msdb.dbo.sysjobsteps)
• PowerShell: "SQLPS (SQLAgent$SS2017@ADAM234)" (Nothing to do here - not enough information. Is there some other way to figure out what job this is?)
• SSIS: "SSIS-{A8B5F2A6-A9DB-4778-B06C-F3E9DD32F2C}some_name" (GUID == package_guid from ssisdb.catalog.packages, maybe??; some_name == ?? maybe a database name?)

[mfuller333]

Agreed, the SSIS stuff gets really hairy quickly, especially when SSIS application servers are involved. Totally stoked about adding the the T-SQL, its an awesome enhancement and will be appreciated by many ! 👍

[mfuller333]

My fault for letting this fall through the cracks, several people have been asking about this enhancement. Is there any reason we can't add the job name at the top level for starters while I dig into the PowerShell and identifying the SSIS packages from the catalog?

[erikdarlingdata]

@mfuller333 have you made any progress on this one? I'm trying to clean up some older issues around here.

[mfuller333]

Sorry, I have been swamped, I will put some effort towards this week.

[mfuller333]

@amachanic For SSIS, the format is as follows: "SSIS-{DTSID} -- ". The GUID DTSID is the default connection manager for the SSIS package that is running.

The only way I found I could get back to the SSIS Project name in the [SSISDB].[internal].[object_parameters] table where this GUID is referenced in the design_default_value column as the Application Name.

This will return the SSIS Project Name.

SELECT
    op.object_name,
    op.design_default_value
FROM
    [SSISDB].[internal].[object_parameters] op
WHERE
    CAST(op.design_default_value AS VARCHAR(MAX)) 
        LIKE '%SSIS-{A8B5F2A6-A9DB-4778-B06C-F3E9DD32F2C}some_name%'

The only issue with this is that the SSISDB needs to be on the same server as the data. This is not always the case, so I don't think there is a real solution for this case.