The case of the missing report

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.

ScreenHunter_44 Feb. 12 16.37

 




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 &lt;&gt;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

Advertisements
About

IT professional with wider interest in technology

Tagged with: , ,
Posted in backup, sql, tsql, Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: