cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Veritas.com
Support

OpsCenter SQL query problem

MatthewN
Level 2

‎03-11-2016 08:07 AM

Hi

 

I have a SQL query im using to generate a report showing last succesful back up after a failure on all clients, looking at data from the last 7 days.

The report is perfect except I want it to not include entries where the last succesfull back was less than 2 days ago.

This is what im using at the moment

SELECT DISTINCT
B.friendlyName as 'Master Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
A.scheduleName as "Schedule Name",
LIST( DISTINCT A.statuscode) as 'Status Code',
            (       select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful' 
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = CKP.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleName = A.scheduleName AND
                              CKP.statusCode = 0  ),
            (       select  UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
                    from   domain_JobArchive BKP
                    where BKP.masterServerId = A.masterServerId AND
                              BKP.Id = BKP.parentJobId AND
                              BKP.clientId = A.clientId AND
                              BKP.policyId = A.policyId AND
                              BKP.policyName = A.policyName AND
                              BKP.policyType = A.policyType AND
                              BKP.scheduleName = A.scheduleName AND
                              BKP.statusCode = 0 )
FROM  "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
          DATEDIFF(day,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 7
AND
(         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = A.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleType = A.scheduleType AND
                               CKP.statusCode = 0 ) >1 AND
A.scheduleName != 'user_backup' AND
A.statusCode not in (0,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2)
Group by A.clientName,A.masterServerId,A.policyName,A.scheduleName,B.friendlyName,policyType,scheduleType,clientid,policyid
ORDER BY B.friendlyName , A.clientName

I have tried changing >1 to >2, I have also tried != (0,1), i have also tried not in (0,1).  The problem is as far as i can tell the section i have changed to bold below is doing nothing. I can even remove this section from the query and it makes no difference to the report. This is the section i believe i can use to filter out 0 and 1 from the last succefull back up field but as it does nothing im not getting very far!


WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
          DATEDIFF(day,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 7
AND
(         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = A.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleType = A.scheduleType AND
                               CKP.statusCode = 0 ) >1 AND
A.scheduleName != 'user_backup' AND
A.statusCode not in (0,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2)
Group by A.clientName,A.masterServerId,A.policyName,A.scheduleName,B.friendlyName,policyType,scheduleType,clientid,policyid
ORDER BY B.friendlyName , A.clientName

 

Every thing I have tried has failed to remove entries where the last succesful back up was less than 2 days.

 

Has anyone get any suggestions or fixes for this. I have next to no sql knowledge and have only delved into this to create a very specific report that can run every day and be sent out to our ops team. Any help will be greatly appreciated.

 

Thanks in advance for any help

 

Matt

0 Kudos
1 REPLY 1

areznik
Level 5

‎03-29-2016 09:20 AM

I think what you need is a "HAVING" clause, think of it as a "WHERE" thats applied after all the aggregation (group by) is already done. 

So you would say something like: 

...
Group by A.clientName,A.masterServerId,A.policyName,A.scheduleName,B.friendlyName,policyType,scheduleType,clientid,policyid
HAVING daysSinceLastSuccess > 2
ORDER BY B.friendlyName , A.clientName

make sure you define a column alias for the column you want to set up a HAVING filter on. For example: 

SELECT DISTINCT
B.friendlyName as 'Master Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
A.scheduleName as "Schedule Name",
LIST( DISTINCT A.statuscode) as 'Status Code',
            (       select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful' 
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = CKP.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleName = A.scheduleName AND
                              CKP.statusCode = 0  ) AS daysSinceLastSuccess,
...
...

 

Hope that helps!

0 Kudos