Index: trunk/DataCheck/QuickLook/FlareAlerts.sh =================================================================== --- trunk/DataCheck/QuickLook/FlareAlerts.sh (revision 18741) +++ trunk/DataCheck/QuickLook/FlareAlerts.sh (revision 18742) @@ -5,89 +5,469 @@ printprocesslog "INFO starting $0" -# binning -bin=20 +emailfrom=dorner@astro.uni-wuerzburg.de +emailto=dorner@astro.uni-wuerzburg.de # get date -# add funtion -night=20160102 +if [ "$1" != "" ] +then + checkstring=`echo $1 | grep -E -o '^20[0-9][0-9][01][0-9][0-3][0-9]$'` + echo $checkstring + if [ "$checkstring" = "" ] + then + night=`date +%Y%m%d --date="-12 HOUR"` + else + night=$1 + fi +else + night=`date +%Y%m%d --date="-12 HOUR"` +fi + +echo "Processing "$night + # get sources for current night from DB (RunInfo) -sourcekey=1 - -# get limits from DB -siglimit=3.0 -exclimit=20 - -# query to get excess rate and significance from DB -query="SELECT o.* FROM" -query=$query" (SELECT fRunID, IF (@os+fOnTimeAfterCuts/60.>"$bin", @sig := fNumSigEvts, @sig := @sig + fNumSigEvts) AS sigevts, " -query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @bg := fNumBgEvts, @bg := @bg + fNumBgEvts) AS bgevts, " -query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @exc := fNumExcEvts, @exc := @exc + fNumExcEvts) AS excevts, " -query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @os := fOnTimeAfterCuts/60., @os := @os + fOnTimeAfterCuts/60.) AS ontime, " -query=$query" @exc/@os*60 AS excrate, " -query=$query" IF (@sig>0 AND @bg*5>0 AND (@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))> 0, Sqrt((@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))*2), 0) AS sig " -query=$query" FROM AnalysisResultsRunLP O LEFT JOIN RunInfo USING (fNight, fRunID) " -query=$query" CROSS JOIN (SELECT @os :=0, @sig :=0, @bg := 0, @exc :=0, @rate :=0) PARAMS " -query=$query" WHERE fNight="$night" AND fSourceKey="$sourcekey") o HAVING ontime > "$bin"*0.9" - -echo $query - -exit - -results=( `sendquery` ) -#echo ${results[@]} - -i=0 -while [ 0 -lt 1 ] +query="SELECT fSourceKey FROM RunInfo WHERE fNight="$night" AND fRunTypeKey=1 AND NOT ISNULL(fSourceKey) GROUP BY fSourceKey" +sourcekeys=( `sendquery` ) + +# some stuff for queries: +ontime="IF(ISNULL(fEffectiveOn), fOnTimeAfterCuts, TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn)" +threshold="IF (ISNULL(fThresholdMinSet), fThresholdMedian, fThresholdMinSet)" +cu="20.0" +corr="1" +# missing: corrected excessrates +# missing: time-dependent CU + +function get_query_nightly_binning() +{ + # query to get information from DB + query="SELECT fSourceKey AS num, " + query=$query"fNight AS night, MIN(fRunID) AS runmin, MAX(fRunID) AS runmax, " + query=$query"MIN(fRunStart) AS start, MAX(fRunStop) AS stop, " + query=$query"ROUND(SUM("$ontime")/3600.,1) AS ontime, " + query=$query"SUM(fNumSigEvts) AS sig, SUM(fNumBgEvts) AS bg, " + query=$query"ROUND(SUM(fNumBgEvts)/SUM("$ontime")*3600,1) AS bgrate, " + query=$query"SUM(fNumExcEvts) AS exc, " + query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts)), 1) AS excerr, " + query=$query"ROUND(SUM(fNumExcEvts)/SUM("$ontime")*3600,1) AS excrate, " + query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600, 1) AS excrateerr, " + query=$query"ROUND(SUM(fNumExcEvts*"$corr")/SUM("$ontime")*3600,1) as corexcrate, " # put here correction factor + query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600*SUM(fNumExcEvts)/SUM(fNumExcEvts*"$corr"), 1) AS corexcrateerr, " # correctionfactor = exc / exc_cor, put here correction factor + query=$query"ROUND(LiMa(SUM(fNumSigEvts), SUM(fNumBgEvts)),1) AS signif, " + query=$query"ROUND(SUM(fNumExcEvts)/SUM("$ontime")*3600/"$cu",1) AS cu, " # make value time dependent + query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600/"$cu", 1) AS cuerr, " # make value time dependent + query=$query"ROUND(SUM(fNumExcEvts*"$corr")/SUM("$ontime")*3600/"$cu",1) as corcu, " # make value time dependent # put here correction factor + query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600*SUM(fNumExcEvts)/SUM(fNumExcEvts*"$corr")/"$cu", 1) AS corcuerr, " # correctionfactor = exc / exc_cor # make value time dependent # put here correction factor + query=$query"MIN(fZenithDistanceMin) as zdmin, MAX(fZenithDistanceMax) as zdmax, " + query=$query"MIN("$threshold") as thmin, MAX("$threshold") as thmax " + query=$query"FROM AnalysisResultsRunLP " + query=$query"LEFT JOIN RunInfo USING (fNight, fRunID) " + query=$query"WHERE fSourceKey="$sourcekey" AND fNight="$night" AND NOT ISNULL(fNumExcEvts) " + query=$query"GROUP BY fNight, fSourceKey " +# query=$query"ORDER BY fRunStart " + query=$query"HAVING ontime > 0.5 " # at least 30 minutes of observation +} + +function get_query_minute_binning() +{ + # set binning + if [ "$1" != "" ] + then + bin2=$1 + else + bin2=$bin + fi + # query to get information from DB + query="SELECT MAX(o.b) AS num, " + query=$query"MIN(o.n) AS night, MIN(o.run) AS runmin, MAX(o.run) AS runmax, " + query=$query"MIN(o.start) AS start, MAX(o.stop) AS stop, " + query=$query"ROUND(SUM(o.ot)/60.,1) AS ontime, " + query=$query"SUM(o.sig) AS sig, SUM(o.bg) AS bg, " + query=$query"ROUND(SUM(o.bg)/SUM(o.ot)*3600,1) AS bgrate, " + query=$query"SUM(o.exc) AS exc, " + query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg)), 1) AS excerr, " + query=$query"ROUND(SUM(o.exc)/SUM(o.ot)*3600,1) AS excrate, " + query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600, 1) AS excrateerr, " + query=$query"ROUND(SUM(o.exccor)/SUM(o.ot)*3600,1) as corexcrate, " + query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600*SUM(o.exc)/SUM(o.exccor), 1) AS corexcrateerr, " # correctionfactor = exc / exc_cor + query=$query"ROUND(LiMa(SUM(o.sig), SUM(o.bg)),1) AS signif, " + query=$query"ROUND(SUM(o.exc)/SUM(o.ot)*3600/o.cu,1) AS cu, " + query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600/o.cu, 1) AS cuerr, " + query=$query"ROUND(SUM(o.exccor)/SUM(o.ot)*3600/o.cu,1) as corcu, " + query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600*SUM(o.exc)/SUM(o.exccor)/o.cu, 1) AS corcuerr, " # correctionfactor = exc / exc_cor + query=$query"MIN(o.zdmin) as zdmin, MAX(o.zdmax) as zdmax, " + query=$query"MIN(o.th) as thmin, MAX(o.th) as thmax " + query=$query"FROM (" + query=$query"SELECT " + query=$query"fRunID AS run, fNight AS n, " + query=$query"@ot:= "$ontime" AS ot, " + query=$query"fRunStart AS start, fRunStop AS stop, " + query=$query"fNumExcEvts AS exc, fNumBgEvts AS bg, fNumSigEvts AS sig, " + query=$query"fNumExcEvts*"$corr" AS exccor, " # put here correction factor + query=$query$cu" as cu, " # make value time dependent + query=$query"fZenithDistanceMin AS zdmin, fZenithDistanceMax AS zdmax, " + query=$query$threshold" AS th, " + query=$query"IF (@night=fNight AND FLOOR((@os+@ot)/"$bin2"./60.)<1, @bl, @bl := @bl + 1) AS b, " + query=$query"IF (@night=fNight AND FLOOR((@os+@ot)/"$bin2"./60.)<1, @os:=@os + @ot, @os := @ot) AS os, @" + query=$query"night :=fNight AS night FROM AnalysisResultsRunLP " + query=$query"LEFT JOIN RunInfo USING (fNight, fRunID) " + query=$query"CROSS JOIN (SELECT @night :=0, @ot :=0, @os :=0, @bl:=0) PARAMS " + query=$query"WHERE fSourceKey="$sourcekey" AND fNight="$night" AND NOT ISNULL(fNumExcEvts) " + if [ "$1" != "" ] + then + query=$query" AND fRunID <="${results[$num+3]} + fi + query=$query" ORDER BY fRunStart " + if [ "$1" != "" ] + then + query=$query"DESC" + fi + query=$query" ) o GROUP BY b HAVING ontime > "$bin2"*0.75 ORDER BY start " + if [ "$1" != "" ] + then + query=$query"DESC" + fi + #echo $query +} + +function evaluate_result() +{ + oldexc=0 + exc=0 + excold=0 + slope=0 + slopeprev=0 + i=0 + # be careful with start and stop (space inbetween) -> 27 columns instead of 25 + while [ 0 -lt 1 ] + do + trigger=0 + num=`echo "$i * 27" | bc -l` + if [ "${results[$num]}" = "" ] + then + break + fi + #night=${results[$num+1]} + runid=${results[$num+2]} + sig=${results[$num+18]} # significance + #exc=${results[$num+14]} # excrate + excold=$exc + exc=${results[$num+19]} # excrate in CU + + if [ "$onlyifhigher" = "yes" ] + then + higher=` echo " $exc > $oldexc " | bc ` + if [ $higher -eq 1 ] + then + # keep old value + oldexc=$exc + fi + fi + + if [ "$bin" = "" ] + then + echo " ontime: "${results[$num+8]}" h" + fi + + # fast rise/decay trigger + if [ $triggertype -eq 3 ] + then + slopeprev=$slope + sigprev=$sig + if [ "$excold" = "0" ] + then + slope=0 + else + slope=`echo " scale=1; ( $exc - $excold ) / ( $bin / 60. ) " | bc -l ` + fi + get_query_minute_binning 60 + #echo $query + results2=( `sendquery` ) + # need to check last hour backward + if [ "${results2[19]}" = "" ] + then + slope60=0 + sig60=0 + else + sig60=${results2[18]} # significance 1h + if [ "${results2[27+19]}" = "" ] + then + # maybe treat this case differently + slope60=${results2[19]} + else + slope60=`echo " ${results2[19]} - ${results2[27+19]} " | bc -l` # ie /1h + fi + fi + #echo "exc "$exc" excold "$excold + #echo "slope "$slope" prev "$slopeprev" sig "$sig" prevsig "$sigprev" sig60 "$sig60" slope60 "$slope60 + fi + # missing: probably one should check also 20 min binning (s example 20170103) + + echo " "$i" "${results[$num+2]}"-"${results[$num+3]}"["${results[$num+8]}"] "$exc" "$sig + + case $triggertype in + 1) #echo "std trigger: criteria ( $exc >= $exclimit && $sig >= $siglimit && $higher )" + trigger=`echo " $exc >= $exclimit && $sig >= $siglimit && $higher " | bc -l` + ;; + 2) #echo "magic 501: criteria ( $exc >= $exclimit && $sig >= $siglimit && $higher )" + trigger=`echo " $exc >= $exclimit && $sig >= $siglimit && $higher " | bc -l` + ;; + 3) #echo "magic fast rise/decay" + trigger=`echo " $slope >= $slopelimit && $slopeprev >= $slopelimit && $slope60 >= $slopelimit && $sig >= $siglimit " | bc -l` + ;; + *) echo $triggertype" not yet implemented" + ;; + esac + + if [ $trigger -eq 1 ] + then + # do whatever to be done to trigger + # - send email/sms / call + # - create amon file + # - prepare email for alert + # - entry in DB + + if [ "$bin" = "" ] + then + # nightly file: simply overwrite, but do not send trigger again + # or check value if it's increasing? + # missing: get information from previous trigger + interpret + triggerfile="/home/fact/flare.alerts/"$night"-"$exclimit"_"$siglimit"-source"$sourcekey".trigger"$triggertype + else + # smaller binning: check if trigger is identical + triggerfile="/home/fact/flare.alerts/"$night"_"`printf %03d $runid`"-"$exclimit"_"$siglimit"-source"$sourcekey".trigger"$triggertype + donetriggerfile=$triggerfile".done" + #ls $donetriggerfile + # write new file only if old files do not agree + if [ "$donetriggerfile" != "" ] && [ -e $donetriggerfile ] + then + diff $donetriggerfile $triggerfile >/dev/null + checkstatus=`echo $?` + if [ $checkstatus -eq 0 ] + then + echo " alert already done "$donetriggerfile + i=`echo $i +1 | bc -l` + continue + fi + fi + if [ -e $triggerfile ] + then + mv $triggerfile $donetriggerfile + fi + fi + #echo $night"_"$runid" "$sourcekey" -> "$triggerfile + echo " writing "$triggerfile + touch $triggerfile + echo "Trigger found: " > $triggerfile + echo "-------------- " >> $triggerfile + echo " type: "$triggertype >> $triggerfile + echo " excess limit: "$exclimit" evts/h" >> $triggerfile + echo " significance limit: "$siglimit" sigma" >> $triggerfile + if [ "$bin" = "" ] + then + echo " nightly binning " >> $triggerfile + else + echo " binning: "$bin" min" >> $triggerfile + fi + echo "Summary of flare event: " >> $triggerfile + echo "----------------------- " >> $triggerfile + echo " source: "$sourcename >> $triggerfile + echo " night: "${results[$num+1]} >> $triggerfile + echo " runs: "${results[$num+2]}" - "${results[$num+3]} >> $triggerfile + echo " start: "${results[$num+4]}" "${results[$num+5]}" UTC" >> $triggerfile + echo " stop: "${results[$num+6]}" "${results[$num+7]}" UTC" >> $triggerfile + if [ "$bin" = "" ] + then + echo " ontime: "${results[$num+8]}" h" >> $triggerfile + else + echo " ontime: "${results[$num+8]}" min" >> $triggerfile + fi + #echo " ontime: "`echo "scale=1; ${results[$num+8]} / 60. " | bc -l`" min" #scale doesn't round properly + echo " signal: "${results[$num+9]}" evts" >> $triggerfile + echo " background: "${results[$num+10]}" evts" >> $triggerfile + echo " bgrate: "${results[$num+11]}" evts/h" >> $triggerfile + echo " exc: "${results[$num+12]}" +- "${results[$num+13]}" evts" >> $triggerfile + echo " excrate: "${results[$num+14]}" +- "${results[$num+15]}" evts/h" >> $triggerfile + echo " corr. excrate: "${results[$num+16]}" - "${results[$num+17]}" evts/h" >> $triggerfile + echo " significance: "${results[$num+18]}" sigma" >> $triggerfile + echo " cu: "${results[$num+19]}" +- "${results[$num+20]}" CU" >> $triggerfile + echo " corr. cu: "${results[$num+21]}" +- "${results[$num+22]}" CU" >> $triggerfile + echo " zd: "${results[$num+23]}" - "${results[$num+24]}" degree" >> $triggerfile + echo " th: "${results[$num+25]}" - "${results[$num+26]}" DAC counts" >> $triggerfile + # additional information fast rise/decay trigger + if [ $triggertype -eq 3 ] + then + echo "Flux doubling/halfing times: " >> $triggerfile + echo "---------------------------- " >> $triggerfile + echo " excess: "$exc >> $triggerfile + echo " excess old: "$excold >> $triggerfile + echo " significance: "$sig >> $triggerfile + echo " significance old: "$sigprev >> $triggerfile + echo " slope: "$excold >> $triggerfile + echo " slope old: "$slopeold >> $triggerfile + echo " excess 60 min: "${results2[19]} >> $triggerfile + echo " excess 60 min old: "${results2[27+19]} >> $triggerfile + echo " slope 60 min: "$slope60 >> $triggerfile + echo " significance 60 min: "$sig60 >> $triggerfile + fi + + if [ "$donetriggerfile" != "" ] && [ -e $donetriggerfile ] + then + diff $donetriggerfile $triggerfile >/dev/null + checkstatus=`echo $?` + if [ $checkstatus -gt 0 ] + then + rm $donetriggerfile + fi + fi + # missing: get summary of whole observation + + # missing AMON cases - create VOEvent-File + + # send email only of $donetriggerfile doesn't exists + ##cat $triggerfile | mail -s 'test flare alert ' -b $emailfrom -r $emailfrom $emailto + #cat $triggerfile | mail -s "test flare alert for $sourcename " $emailto + # that's also the cases for making a call + + fi + + # counter + i=`echo $i +1 | bc -l` + done + + echo " found "$i" data point(s)." + echo "" +} + +for sourcekey in ${sourcekeys[@]} +do + query="SELECT fSourceName FROM Source WHERE fSourceKey="$sourcekey + sourcename=`sendquery` + + if [ $sourcekey -ne 1 ] + then + continue + fi + echo "Evaluation for $sourcename ... " + + # missing: get limits from DB (structure needs to be defined) + + # triggers in the frame of the MoU in the gamma-ray community + triggertype=1 + # limits + siglimit=3.0 # sigma + exclimit=0.5 # CU + siglimit=2.0 + exclimit=0.5 + if [ $sourcekey -eq 1 ] || [ $sourcekey -eq 2 ] + then + exclimit=3.0 # CU + fi + # only if rate goes even higher, we have to react + onlyifhigher="yes" + higher=1 + + echo "[General gamma-ray MoU]" + echo " nightly binning..." + bin= + # checking nightly binning + get_query_nightly_binning + #echo $query + results=( `sendquery` ) + #echo ${results[@]} + evaluate_result + #continue + + # binning + bin=20 + echo " "$bin" min binning..." + + get_query_minute_binning + #echo $query + results=( `sendquery` ) + #echo ${results[@]} + evaluate_result + + # triggers to MAGIC + # Mrk 501 proposal + triggertype=2 + if [ $sourcekey -eq 2 ] + then + siglimit=3.0 + exclimit=2.0 # cu + echo "[Trigger to MAGIC 501 proposal]" + echo " nightly binning..." + bin= + # checking nightly binning + get_query_nightly_binning + #echo $query + results=( `sendquery` ) + #echo ${results[@]} + evaluate_result + #continue + + # binning + bin=20 + echo " "$bin" min binning..." + + get_query_minute_binning + #echo $query + results=( `sendquery` ) + #echo ${results[@]} + evaluate_result + fi + # Mother of ToO - fast rise/decay + # sources: Mrk 421, Mrk 501, 2344, 1959 + triggertype=3 + if [ $sourcekey -eq 1 ] || [ $sourcekey -eq 2 ] || [ $sourcekey -eq 3 ] || [ $sourcekey -eq 7 ] + then + echo "[Trigger to MAGIC - fast rise/decay]" + # keep thresholds low (or do not use in evaluation) + siglimit=2.0 + exclimit=0.5 + # limits in slope + slopelimit=1.0 # 1CU/h + siglimith=3.0 # 1 sigma in 1 hour + + # binning + bin=30 + echo " "$bin" min binning..." + get_query_minute_binning + #echo $query + results=( `sendquery` ) + #echo ${results[@]} + evaluate_result + fi + + # Triggers to AMON + triggertype=4 + echo "[Trigger to AMON]" + echo " still to be defined" + # missing: trigger limits and binning still to be defined + # also nightly? + # sub-threshold-triggers? + # FP-rate? + siglimit=2.0 + exclimit=0.5 + onlyifhigher="no" + bin=20 + + echo "" + echo "" + done + +finish + +# missing information: mjd, obs-summary, weather info (clouds? dust?) +# prepare directly template for email + +# missing: error emails in case no DB content / no QLA +# calculate delay of QLA and send email if > 30 Min + +# for archival testing: +for (( i=0; i < 100 ; i++)) do - num=`echo "$i * 7" | bc -l` - if [ "${results[$num]}" = "" ] - then - break - fi - runid=${results[$num]} - sig=${results[$num+6]} - exc=${results[$num+5]} - - trigger=`echo " $exc > $exclimit && $sig > $siglimit " | bc -l` - -# echo $i" "$num" "$exc" "$sig - if [ $trigger -eq 1 ] - then - # do whatever to be done to trigger - # - send email/sms / call - # - create amon file - # - prepare email for alert - echo $runid" "$exc" "$sig" -> "$trigger - triggerfile="/home/fact/flare.alerts/"$night"_"$runid"_"$sourcekey".trigger" - touch $triggerfile - fi - # counter - i=`echo $i +1 | bc -l` + date=`date --date="-${i}days" +%Y%m%d` + /home/fact/SW.automatic.processing/DataCheck/QuickLook/FlareAlerts.sh $date done -finish - - -# store limits in DB? -> Source table -# run for current night -# run for each source in the current night -# error emails in case no DB content / no QLA -# email in case of flare -# add further information -# obstime (start, stop, duration), mjd, zd-range, light-condition/threshold-range, weather, (check template) -# binning as command-line-option or loop for different binnings -# create VO-Event file -# create template-email -# add logging -# add certaindate -# implement option for archival testing - -# calculate delay of QLA and send email if > 30 Min - -# queries: -# http://stackoverflow.com/questions/32247263/mysql-select-last-n-rows-while-sum-less-then-number -# http://stackoverflow.com/questions/14890486/limiting-the-rows-to-where-the-sum-a-column-equals-a-certain-value-in-mysql - - - +