Since I don't have access to your system, I can only be of limited help here. You have to look at the query and correlate that with what your front-end client wants to get.
A query has several sections/logical pieces. In the code below, I have intended the text so you can distinguish different sections .
Look at the first section which is the one that starts with SELECT. It returns 8 columns which I have numbered. Anything after a dash-dash ( -- ) is a comment, so you can ignore all of that. Are those 8 columns the ones your client front end expects? For example, there is a column called Object_Folder_Path. That is probably what you are seeing with the full path name. The column that I added is #6 named Object_Type_Name.
You don't have to try to become an expert, but read up a little bit on SELECT statements, for example here.
-----------------------------------------------------------------------------
SELECT ADS_EVENT.Top_Folder_Name , -- 1
ADS_EVENT.[Object_Name] , -- 2
ADS_EVENT.Object_Folder_Path , -- 3
ADS_EVENT.User_Name , -- 4
CAST(ADS_EVENT.Start_Time AS CHAR) , -- 5
REVERSE(LEFT(b, CHARINDEX('/', b + '/') - 1)) AS Object_Type_Name , -- 6
CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 , -- 7
CASE ADS_STATUS_STR.Status_ID
WHEN 0 THEN 'successful'
ELSE 'failed'
END -- 8
-----------------------------------------------------------------------------
FROM ADS_EVENT_TYPE_STR
INNER JOIN ADS_EVENT_TYPE ON ( ADS_EVENT_TYPE.Event_Type_ID = ADS_EVENT_TYPE_STR.Event_Type_ID
AND ADS_EVENT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_EVENT ON ( ADS_EVENT.Event_Type_ID = ADS_EVENT_TYPE.Event_Type_ID )
INNER JOIN ADS_OBJECT_TYPE_STR ON ( ADS_EVENT.Object_Type_ID = ADS_OBJECT_TYPE_STR.Object_Type_ID
AND ADS_OBJECT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_STATUS_STR ON ( ADS_EVENT.Event_Type_ID = ADS_STATUS_STR.Event_Type_ID
AND ADS_EVENT.Status_ID = ADS_STATUS_STR.Status_ID
AND ADS_STATUS_STR.Language = 'en'
)
-- ADDED THIS CROSS APPLY
CROSS APPLY ( VALUES
( STUFF(ADS_EVENT.Object_FolderPath, 1,
CHARINDEX('/', ADS_EVENT.Object_FolderPath + '/'), '')) ) AS B ( b )
-----------------------------------------------------------------------------
WHERE ( ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run' )
AND DATEPART(yyyy, ADS_EVENT.Start_Time) >= 2016
AND ADS_EVENT.Top_Folder_Name NOT IN ( '', 'Auditing',
'Feature Samples',
'Functions & Templates',
'IT SERVICES',
'IT SERVICES - Copy (1)',
'IT SERVICES - OLD',
'IT SERVICES [1]', 'itelliTest',
'LCM', 'Report Conversion Tool',
'Report Samples',
'Report Samples - Copy (1)',
'Web Intelligence Samples' );
-----------------------------------------------------------------------------