select downlinkid, time, filename, nfiles, percentDone, percentFailed, runid, status, nevts, l0key, moot_alias as runintent, runstatus, runquality,
GLAST_DP_TEST.GLAST_UTIL.GetTimeFromEpochMS(evtutc0) as time0,
GLAST_DP_TEST.GLAST_UTIL.GetTimeFromEpochMS(evtutc1) as time1,
GLAST_DP_TEST.PII.GetStreamProgress('HalfPipe',downlinkid) as halfPipeProgress,
( select GLAST_DP_TEST.PII.getStreamProgress(stream) from GLAST_DP_TEST.stream
join GLAST_DP_TEST.task using (task) where parentstream=GLAST_DP_TEST.PII.GetMostRecentStreamPK('L1Proc',downlinkid)
and taskname='doRun' and streamId=runid ) as l1ProcProgress,
( select stream from GLAST_DP_TEST.stream
join GLAST_DP_TEST.task using (task) where parentstream=GLAST_DP_TEST.PII.GetMostRecentStreamPK('L1Proc',downlinkid)
and taskname='doRun' and streamId=runid ) as l1ProcStreamId,
(select count(1) || ':' || sum(case when lvl_fk=1 then 1 else 0 end) || ':' || sum(case when lvl_fk=2 then 1 else 0 end) || ':' || sum(case when lvl_fk=3 then 1 else 0 end) || ':' || sum(case when lvl_fk=4 then 1 else 0 end)
from isoc_flight.netlog_evnt where tgt = filename and tevnt >= ( timestamp'1970-01-01 00:00:00' + numtodsinterval( time, 'SECOND' ) ) ) as fastCopyLogs,
( select count(1) || ':' || sum(case when var.varname = 'L1_Alarm_undefined' then to_number(var.value) else 0 end) || ':' || sum(case when var.varname = 'L1_Alarm_error' then to_number(var.value) else 0 end) || ':' || sum(case when var.varname = 'L1_Alarm_warning' then to_number(var.value) else 0 end) || ':' || sum(case when var.varname = 'L1_Alarm_clean' then to_number(var.value) else 0 end)
from GLAST_DP_TEST.PROCESSINSTANCEVAR var
join GLAST_DP_TEST.processinstance processinstance on ( processinstance.processinstance = var.processinstance )
join GLAST_DP_TEST.stream stream on (stream.stream = processinstance.stream)
where stream.streamid = runid and stream.parentstream=GLAST_DP_TEST.PII.GetMostRecentStreamPK('L1Proc',downlinkid) and var.varname like 'L1_Alarm%' ) as l1ProcLogs,
( select sum(case when datasetdatatype = 'FASTMONHIST' then 1 else 0 end) || ':' || sum(case when datasetdatatype = 'RECONHIST' then 1 else 0 end) || ':' ||
sum(case when datasetdatatype = 'DIGIHIST' then 1 else 0 end) || ':' || sum(case when datasetdatatype = 'CALHIST' then 1 else 0 end)
from GLAST_DP_TEST.dataset join GLAST_DP_TEST.datasetmetanumber downlink on ( downlink.dataset = dataset.dataset and downlink.metaname = 'nDownlink' )
where runmin = runid and downlink.metavalue = downlinkid ) as datasets,
GLAST_DP_TEST.PII.GetStreamProgress('GRB_blind_search',downlinkid) as grbSearchProgress
from (select time, filename, percentDone, percentFailed, nfiles, l0key, downlinkid from isoc_flight.fcopy_incoming incoming, (
select max(incoming.incoming_pk) l0key, count(icdfile.filename) nfiles, max(incoming.treceive) time, max(downlink_id) downlinkid,
sum(case when jobstate_fk = 7 then 1 else 0 end)/count(icdfile.filename)*100 percentDone,
sum(case when jobstate_fk = 9 then 1 else 0 end)/count(icdfile.filename)*100 percentFailed
from isoc_flight.fcopy_incoming incoming join isoc_flight.fcopy_icdfile icdfile on ( icdfile.incoming_fk=incoming.incoming_pk )
left outer join isoc_flight.fcopy_l0dispatch l0dispatch on (l0dispatch.incoming_fk = incoming.incoming_pk )
where treceive > ? and treceive < ? and icdfile.filename like '%VC08%' group by icdfile.incoming_fk)
where incoming.incoming_pk = l0key and downlinkid is not null ) left outer join
( select id rundownlinkid, startedat runid, status, nevts, evtutc0, evtutc1, moot_alias from isoc_flight.glastops_DOWNLINK downlink
join isoc_flight.glastops_DOWNLINK_ACQSUMMARY bridge on downlink.ID=bridge.DOWNLINK_ID
join isoc_flight.glastops_ACQSUMMARY acqsum on ( acqsum.scid = bridge.scid and acqsum.startedat = bridge.startedat)
) rundownlink on (downlinkid = rundownlink.rundownlinkid )
left outer join ( select r.runid as runqualityid, r.runstatus as runstatus, r.runquality as runquality from GLAST_DP_TEST.Run r ) runq on (runq.runqualityid = rundownlink.runid )order by time desc |