XMA: Example of a SQL query to determine the number of unsent records in the SYSLOGQ table

XMA: Example of a SQL query to determine the number of unsent records in the SYSLOGQ table

The sample query statements below can also be used as an input file, for example, SQLCI /in <in-file>/ on the XMA DB subvolume.

The query to determine unsent records in the SYSLOGQ table.
set list_count 0;
set headings off;
select cast (ALERTEDGMT as datetime year to day)
,count (*)
,min(ENTRYGMT)
,max(ENTRYGMT)
-- from <$vol>.<xmadat>.SYSLOGQ
from SYSLOGQ
where
cast (ALERTEDGMT as datetime year to day) = datetime "1970-01-01" year to day
and PART = 0
group by 1
order by 1
browse access;
name col 1 agdt;
name col 2 thecount;
name col 3 oldest;
name col 4 newest;
TITLE
"SYSLOGQ File", skip 1,
"Count and Oldest and Newest Unsent Records", skip 1,
"Date: ", CURRENT_timestamp AS DATE "m2/d2/Y4", SPACE 5,
"Time: ", CURRENT_timestamp AS TIME "HP2:M2";
Detail
"Number of Unsent Records", skip 1,
"------------------------", skip 1,
thecount, skip 2,
"Unsent Record Date Range", skip 1,
"------------------------", skip 1,
"Oldest Unsent Record: ", oldest, skip 1,
"Newest Unsent Record: ", newest;
list all;

< sample output >
SYSLOGQ File
Count and Oldest and Newest Unsent Records
Date: 08/01/2023     Time: 05:23 PM

Number of Unsent Records
------------------------
                  19

Unsent Record Date Range
------------------------
Oldest Unsent Record: 2023-07-24:22:14:12.875461
Newest Unsent Record: 2023-07-24:22:25:42.992231

--- 1 row(s) selected.



The query to determine unsent records in the SYSLOGQ table grouped by IPALERT_PREFIX defined in the fitler.

set list_count 0;
set headings off;
select cast (ALERTEDGMT as datetime year to day)
,prefix
,count (*)
,min(ENTRYGMT)
,max(ENTRYGMT)
-- from <$vol>.<xmadat>.SYSLOGQ
from syslogq
where
cast (ALERTEDGMT as datetime year to day) = datetime "1970-01-01" year to day
and part = 0
group by 1,2
order by 1,2
browse access;
name col 1 agdt;
name col 2 pref;
name col 3 thecount;
name col 4 oldest;
name col 5 newest;
TITLE
"SYSLOGQ File", skip 1,
"Count and Oldest and Newest Unsent Records", skip 1,
"Date: ", CURRENT_timestamp AS DATE "m2/d2/Y4", SPACE 5,
"Time: ", CURRENT_timestamp AS TIME "HP2:M2:S2";
Detail
"Prefix                                  ", " Number of Unsent Records",  skip 1,
"----------------------------------------", " ------------------------",  skip 1,
pref as a40, thecount, skip 2,
"Unsent Record Date Range", skip 1,
"------------------------", skip 1,
"Oldest Unsent Record: ", oldest, skip 1,
"Newest Unsent Record: ", newest;
list all;

< sample output >
SYSLOGQ File
Count and Oldest and Newest Unsent Records
Date: 08/01/2023     Time: 05:59:11 PM

Prefix                                   Number of Unsent Records
---------------------------------------- ------------------------
<130> XMA EMS                                               1

Unsent Record Date Range
------------------------
Oldest Unsent Record: 2023-08-01:00:57:39.992062
Newest Unsent Record: 2023-08-01:00:57:51.147672
Prefix                                   Number of Unsent Records
---------------------------------------- ------------------------
<130> XMA SAFEGUARD                                         5

Unsent Record Date Range
------------------------
Oldest Unsent Record: 2023-08-01:00:56:13.421770
Newest Unsent Record: 2023-08-01:00:59:11.616335

--- 2 row(s) selected.
>>