Following will work for a specific day. Note the times 16:30:00 to 16:29:59 are the ones that work for my setup.
Select count(message) as "SUCCESSFUL CLIENT BACKUPS!!" from actlog where message like 'ANR2507I%' and (date_time between TIMESTAMP ('2019-06-22 16:30:00') and TIMESTAMP ('2019-06-23 16:29:59')) and message not like '%Administrator%'
Select count(message) as "FAILED CLIENT BACKUPS!!" from actlog where message like 'ANR2579E%' and (date_time between TIMESTAMP ('2019-06-22 16:30:00') and TIMESTAMP ('2019-06-23 16:29:59')) and message not like '%Administrator%'
Select count(message) as "MISSED CLIENT BACKUPS!!" from actlog where message like 'ANR2578%' and (date_time between TIMESTAMP ('2019-06-22 16:30:00') and TIMESTAMP ('2019-06-23 16:29:59')) and message not like '%Administrator%'
SELECT count(node_name) as "TOTAL SCHEDULED CLIENT BACKUPS!!" FROM EVENTS WHERE (SCHEDULED_START between TIMESTAMP ('2019-06-22 16:30:00') and TIMESTAMP ('2019-06-23 16:29:59')) and node_name is not null
---------------------------------------------------------------------
Following is a daily report that can be put into the Operations center reports. Or put into a script. The times used in the report are specific to my setup.
SUCCESSFUL CLIENT BACKUPS
Select count(message) as "SUCCESSFUL CLIENT BACKUPS!!" from actlog where message like 'ANR2507I%' and date_time between (current DATE - 1 days) CONCAT ' 16:00:00' AND (current DATE - 0 days) CONCAT ' 08:00:00' and message not like '%Administrator%'
FAILED CLIENT BACKUPS
Select count(message) as "FAILED CLIENT BACKUPS!!" from actlog where message like 'ANR2579E%' and date_time between (current DATE - 1 days) CONCAT ' 16:00:00' AND (current DATE - 0 days) CONCAT ' 08:00:00' and message not like '%Administrator%'
FAILED CLIENT BACKUPS DISTINCT
select distinct count(substr(char(message),uno+9,dos-uno-9)) from (Select message, LOCATE_IN_STRING(MESSAGE,'Schedule ') as uno,LOCATE_IN_STRING(MESSAGE,' in domain') as dos from actlog where message like 'ANR2579E%' and date_time between (current DATE - 1 days) CONCAT ' 16:00:00' AND (current DATE - 0 days) CONCAT ' 08:00:00' and message not like '%Administrator%')
MISSED CLIENT BACKUPS
Select count(message) as "MISSED CLIENT BACKUPS!!" from actlog where message like 'ANR2578%' and date_time between (current DATE - 1 days) CONCAT ' 16:00:00' AND (current DATE - 0 days) CONCAT ' 08:00:00' and message not like 'Administrator'
STILL RUNNING CLIENT BACKUPS COUNT
select count(client_name) as "STILL RUNNING CLIENT BACKUPS!!" from (select DISTINCT t1.client_name, t2.node_name, session_type from sessions t1 left join nodes t2 on t1.client_name=t2.node_name where t1.client_name=t2.node_name and session_type='Node')
TOTAL SCHEDULED CLIENT BACKUPS
SELECT COUNT(SCHEDULE_NAME) as "TOTAL SCHEDULED CLIENT BACKUPS!!" FROM EVENTS WHERE (SCHEDULED_START between (current DATE - 1 days) CONCAT ' 08:00:01' AND (current DATE - 0 days) CONCAT ' 08:00:00') and node_name is not null
STILL RUNNING CLIENT BACKUPS LIST
select DISTINCT t1.client_name, t2.node_name from sessions t1 left join nodes t2 on t1.client_name=t2.node_name where t1.client_name=t2.node_name and t1.SESSION_TYPE='Node'
MISSED BACKUP SCHEDULES
select substring( message, 19, LOCATE_IN_STRING(message, ' in domain')-19) as node_n from (Select message from actlog where message like 'ANR2578%' and date_time between (current DATE - 1 days) CONCAT ' 16:00:00' AND (current DATE - 0 days) CONCAT ' 08:00:00' and message not like 'Administrator') order by node_n
------------------------------
Lynn Hall
ITS 4
OCIO State of Iowa
Des Moines IA
515 669 4325
------------------------------
Original Message:
Sent: Wed December 18, 2019 10:47 AM
From: Prem Singh
Subject: how to get specific months or day backup status on SPP
Hi All,
I am trying to figure out is there any way to get specific day or month backup status report on SPP.
------------------------------
Thanks
Prem
------------------------------