A log many tools and applications exist which are usually missing that report that you exactly need. It can even be missing more complex stuff like screen and functionality but then it would be outright rejected but I have come across a few situations where the application records the right data, does the right job overall but is missing that “killer” report.
This calls for custom reporting and many a times it is not natively supported via API’s or is encouraged by the developer but yet still is possible due to the backend being in SQL or whatever database tool which you can probe, profile and prod for clues and do up your own report.
Such was a recent case with Arcserve the backup utility with a colleague who was impressed by its capabilities but couldn’t get the right information as and when required in the right format.
I put on my Software engineer hat (reverse engineer rather) and concluded with a piece of code which with a SSRS report kind of does the job quite adequately.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Sp_AllJobsBackupStatus]</code> AS select [arcserveUDP].[dbo].[as_edge_policy].name PolicyName, t2.planUUID, ehost.rhostname HostName, esxhost.esxhost ESX, case When T2.jobStatus =1 then 'Success' When T2.jobStatus <>1 then 'Not Successful' end JobStatusDesc , datediff(dd,t2.jobUTCEndTime,getdate()) NoOfDaysSince , t2.jobUTCEndTime StartTime, t2.jobUTCEndTime EndTime From (select T.* , row_number() over (partition BY jobType, agentId ORDER BY jobUTCStartTime DESC) rowno From(select id, jobId, jobStatus,jobMethod , jobUTCStartTime , jobUTCEndTime , agentId, serverid, planuuid, (CASE WHEN jobType in (11,15,16,20) THEN 11 --catalog , catalog on demond , vm catalog , vm catalog ondemond WHEN jobType in (30,31,32) THEN 30 --rps merge , merge , vm merge WHEN jobType in (40 ,41) THEN 40 --conversion and rps conversion WHEN jobType in (0 , 3) THEN 0 --backup and vm backup ELSE jobType END) AS jobType from [arcserveUDP].[dbo].as_edge_d2dJobHistory_lastJob )T)T2 right outer join [arcserveUDP].[dbo].as_edge_host ehost on ( t2.serverid = ehost.rhostid) left outer join [arcserveUDP].[dbo].[as_edge_esx_host_map] esxhost on ( esxhost.hostid = ehost.rhostid ) left outer join [arcserveUDP].[dbo].[as_edge_policy] on ([arcserveUDP].[dbo].[as_edge_policy].Uuid = t2.planUUID) Where T2.rowno=1 and jobType =0 order by PolicyName desc, ehost.rhostname