02-13-2014 07:48 AM
I'm new to SSR 2013 about a month or so but have become pretty familiar with setting up and configuring it and rehabiliting clients. My management is asking for a backup status report for a specific date range. I was surprised to find out that the reporting features doesn't include any such report. I'm not familiar or well versed with creating and customizing SQL queries and the option to create new reports isn't very user friendly. I contacted Techncial Support and the tech working with me told me there are only the built-in reports. I was wondering if someone has created a similar SQL query that they could share?
Solved! Go to Solution.
03-09-2014 10:26 PM
Here we go:
declare @RowCnt int
declare @MaxRows int
declare @SERVER nvarchar(max)
declare @mon varchar (max)
declare @year varchar (max)
declare @counter int
declare @date1 varchar(max)
declare @date2 varchar(max)
declare @date3 varchar(max)
declare @date4 varchar(max)
declare @nobackupfordays int
set @nobackupfordays=2
select @RowCnt = 1
declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , Name varchar(max))
insert into @Import (Name) SELECT distinct com.[Name] FROM vComputerSummaryWithSupportedBESR as
com
select @MaxRows=count(*) from @Import
create table #tempmk ([Guid] uniqueidentifier , [Name] varchar(max), [Fehler] varchar(max))
while @RowCnt <= @MaxRows
begin
select @SERVER = Name from @Import where rownum = @RowCnt
set @year = datepart (yyyy,(SELECT DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
set @mon=datepart (mm,(SELECT DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
set @counter = 0
while @counter < (datepart (dd,GETDATE())-@nobackupfordays)
begin
set @counter = @counter + 1
set @date1=@year+'-'+@mon+'-'+cast(@counter as varchar)+' 00:00:00.000'
set @date2=@year+'-'+@mon+'-'+cast(@counter+@nobackupfordays as
varchar)+' 23:59:59.999'
set @date3=cast(@counter as varchar)+'.'+@mon+'.'+@year
set @date4=cast(@counter+@nobackupfordays as varchar)+'.'+@mon+'.'+@year
if not exists (SELECT distinct
com.[Guid],com.[Name],img.[DisplayName],img.State,img.[Timestamp]
FROM [Symantec_CMDB].[dbo].[vComputerSummary] as com inner join
[Symantec_CMDB].[dbo].[Inv_BESR_Image] as img on img.[_ResourceGuid] = com.guid
where com.[Name]=@SERVER and img.[Timestamp] between CAST(@date1
AS datetime) and CAST(@date2 AS datetime))
begin
INSERT INTO #tempmk ("Guid", "Name","Fehler") SELECT distinct
com.[Guid], com.[Name], 'Kein Backup zwischen '+@date3+' und '+@date4 FROM
[Symantec_CMDB].[dbo].[vComputerSummary] as com where com.[Name]=@SERVER
end
end
set @RowCnt = @RowCnt + 1
end
select [Guid], [Name], [Fehler] from #tempmk
drop table #tempmk
02-16-2014 07:18 PM
Wanted to confirm if you are using Management Solution for SSR or standalone SSR and any specific reason to create report specific to date range ?
02-23-2014 07:10 PM
You can include your Ideas on the below link
BNR Ideas link for enhancement : https://www-secure.symantec.com/connect/backup-and-recovery/ideas
02-23-2014 10:34 PM
Not exactly that but one that shows all clients having missing backups for more than 2 consecutive days.
03-05-2014 10:45 PM
Do you still need this query as an example from me?
03-06-2014 05:20 AM
Markus-
My apologies on not responding sooner but I would like so see this query example if you have it.
03-09-2014 10:26 PM
Here we go:
declare @RowCnt int
declare @MaxRows int
declare @SERVER nvarchar(max)
declare @mon varchar (max)
declare @year varchar (max)
declare @counter int
declare @date1 varchar(max)
declare @date2 varchar(max)
declare @date3 varchar(max)
declare @date4 varchar(max)
declare @nobackupfordays int
set @nobackupfordays=2
select @RowCnt = 1
declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , Name varchar(max))
insert into @Import (Name) SELECT distinct com.[Name] FROM vComputerSummaryWithSupportedBESR as
com
select @MaxRows=count(*) from @Import
create table #tempmk ([Guid] uniqueidentifier , [Name] varchar(max), [Fehler] varchar(max))
while @RowCnt <= @MaxRows
begin
select @SERVER = Name from @Import where rownum = @RowCnt
set @year = datepart (yyyy,(SELECT DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
set @mon=datepart (mm,(SELECT DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
set @counter = 0
while @counter < (datepart (dd,GETDATE())-@nobackupfordays)
begin
set @counter = @counter + 1
set @date1=@year+'-'+@mon+'-'+cast(@counter as varchar)+' 00:00:00.000'
set @date2=@year+'-'+@mon+'-'+cast(@counter+@nobackupfordays as
varchar)+' 23:59:59.999'
set @date3=cast(@counter as varchar)+'.'+@mon+'.'+@year
set @date4=cast(@counter+@nobackupfordays as varchar)+'.'+@mon+'.'+@year
if not exists (SELECT distinct
com.[Guid],com.[Name],img.[DisplayName],img.State,img.[Timestamp]
FROM [Symantec_CMDB].[dbo].[vComputerSummary] as com inner join
[Symantec_CMDB].[dbo].[Inv_BESR_Image] as img on img.[_ResourceGuid] = com.guid
where com.[Name]=@SERVER and img.[Timestamp] between CAST(@date1
AS datetime) and CAST(@date2 AS datetime))
begin
INSERT INTO #tempmk ("Guid", "Name","Fehler") SELECT distinct
com.[Guid], com.[Name], 'Kein Backup zwischen '+@date3+' und '+@date4 FROM
[Symantec_CMDB].[dbo].[vComputerSummary] as com where com.[Name]=@SERVER
end
end
set @RowCnt = @RowCnt + 1
end
select [Guid], [Name], [Fehler] from #tempmk
drop table #tempmk
03-12-2014 11:29 AM
Thanks everyone for you participation!
03-12-2014 03:17 PM
Its always a pleasure to help !
03-18-2014 07:32 PM
Thanks Markus...That was wonderful to see this may help others