Highlighted

Custom SQL Query for generating Backup Status by a date ranges

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?

 

 

 

1 Solution

Accepted Solutions
Accepted Solution!

Here we go: declare @RowCnt

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

View solution in original post

9 Replies

Wanted to confirm if you are

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 ?

You can include your Ideas on

You can include your Ideas on the below link

BNR Ideas link for enhancement : https://www-secure.symantec.com/connect/backup-and-recovery/ideas

Not exactly that but one that

Not exactly that but one that shows all clients having missing backups for more than 2 consecutive days.

Do you still need this query

Do you still need this query as an example from me?

Markus- My apologies on not

Markus-

My apologies on not responding sooner but I would like so see this query example if you have it.

Accepted Solution!

Here we go: declare @RowCnt

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

View solution in original post

Thanks everyone for you

Thanks everyone for you participation!

Its always a pleasure to help

Its always a pleasure to help !

Thanks Markus...That was

Thanks Markus...That was wonderful to see this may help others