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 > ? 1212379515 and treceive < ? 1214971515 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 |