cancel
Showing results for 
Search instead for 
Did you mean: 

Custom SQL Query for generating Backup Status by a date ranges

mvwbackupguy
Level 2
Certified

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 ACCEPTED SOLUTION

Accepted Solutions

Markus_Koestler
Moderator
Moderator
   VIP   

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 9

TRaj
Level 6
Employee Accredited

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 ?

TRaj
Level 6
Employee Accredited

You can include your Ideas on the below link

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

Markus_Koestler
Moderator
Moderator
   VIP   

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

Markus_Koestler
Moderator
Moderator
   VIP   

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

mvwbackupguy
Level 2
Certified

Markus-

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

Markus_Koestler
Moderator
Moderator
   VIP   

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

mvwbackupguy
Level 2
Certified

Thanks everyone for you participation!

Markus_Koestler
Moderator
Moderator
   VIP   

Its always a pleasure to help !

TRaj
Level 6
Employee Accredited

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