TWiki > Accounting Web>GratiaReportGuide (11 Dec 2008, KyleGross)EditAttach

Gratia Report Guide

Report Reference
Report Name   Query Text Drill-Through Report(s)
customSqlReport  

DailyJobsByFacility   call DailyJobsByFacility(?,?,?,?,?) DailyJobsByProbe -L
DailyJobsByFacility -X
DailyJobsByFacilityAndDate   call DailyJobsByFacilityAndDate(?,?,?) DailyJobsByProbe -L
DailyJobsByCETable -Y-error
DailyJobsByProbe   call DailyJobsByProbe(?,?,?,?,?) DailyJobsByProbe -L
DailyJobsByProbe -Y
DailyJobsByProbeAndDate   call DailyJobsByProbeAndDate(?,?,?) DailyJobsByProbe -L
DailyJobsByVO   call DailyJobsByVO(?,?,?,?,?) DailyJobsByProbe -L
DailyJobsByVO -Y
DailyJobsByVOAndDate   call DailyJobsByVOAndDate(?,?,?) DailyJobsByProbe -L
DailyJobsByVO -X
DailyUsageByFacility   call DailyUsageByFacility(?,?,?,?,?) DailyJobsByProbe -L
DailyUsageByFacility -X
DailyUsageByFacilityAndDate   call DailyUsageByFacilityAndDate(?,?,?) DailyUsageByProbe -L
DailyUsageByCETable -Y-error
DailyUsageByProbe   call DailyUsageByProbe(?,?,?,?,?) DailyJobsByProbe -L
DailyUsageByProbe -Y
DailyUsageByProbeAndDate   call DailyUsageByProbeAndDate(?,?,?) DailyUsageByProbe -Y
DailyUsageBySite   select JobUsageRecord.SiteName, Date( JobUsageRecord.EndTime) as EndTime,
sum( JobUsageRecord.WallDuration),
sum( JobUsageRecord.CpuUserDuration+ JobUsageRecord.CpuSystemDuration)
from JobUsageRecord where EndTime >= ? and EndTime <= ?
group by date_format( JobUsageRecord.EndTime,?), JobUsageRecord.SiteName
order by date_format( JobUsageRecord.EndTime,?)		 DailyJobsByProbe -L
DailyUsageBySite -X
DailyUsageByVO   select V.VOName as voname, Date( V.EndTime) as EndTime, sum( V.WallDuration) as WallDuration, sum( V.CpuUserDuration+ V.CpuSystemDuration) as systemduration from VOProbeSummary V where (? <= V.EndTime and V.EndTime <= ?) group by date_format( V.EndTime, ?), V.VOName order by date_format( V.EndTime, ?) DailyJobsByProbe -L
DailyUsageByVO -X
DailyUsageByVOAndDate   call DailyUsageByVOAndDate(?,?,?) DailyJobsByProbe -L
DailyUsageByVO -Y
DateSummary1a   SELECT sum( J.CpuUserDuration+ J.CpuSystemDuration)/(3600*1000) as cpuabs, date_format( J.EndTime, "%Y-%m") as yearmonth, J.VOName as voname, J.LocalUserID as locuserid, sum( J.CpuUserDuration+ J.CpuSystemDuration) *C.!BenchmarkScore/(3600*1000) as cpunorm FROM JobUsageRecord J, CpuInfo C, CEProbes P, CETable CE where J.HostDescription = C.NodeName and date_format( J.EndTime, "%Y-%m") = ?

and J.ProbeName = P.ProbeName and P.facility_id = CE.facility_id and facility_name = ?

and J.VOName = ? group by yearmonth, locuserid order by yearmonth
JobsByFacility   call JobsByFacility(?,?,?,?) JobsByProbe -Y
JobsByProbe   select CEProbes.ProbeName, sum( ProbeSummary.Njobs) from CETable, CEProbes, ProbeSummary where (? = 'All' or CETable.facility_name = ?) and CEProbes.facility_id = CETable.facility_id and ProbeSummary.!ProbeName = CEProbes.!ProbeName and (date(?) <= ProbeSummary.EndTime and ProbeSummary.!EndTime <= date(?)) group by CEProbes.ProbeName order by CEProbes.ProbeName
JobsByProbeNoFacility   call JobsByProbeNoFacility(?,?,?,?) UsageReport -Y
JobsBySite   select JobUsageRecord.SiteName, sum( JobUsageRecord.Njobs) from JobUsageRecord where (? <= JobUsageRecord.EndTime and JobUsageRecord.EndTime <= ?) group by JobUsageRecord.SiteName order by JobUsageRecord.SiteName JobsByUser -Y
JobsByUser   select JobUsageRecord.CommonName as UserName, sum( JobUsageRecord.Njobs) from JobUsageRecord where (? <= JobUsageRecord.EndTime and JobUsageRecord.EndTime <= ?) group by JobUsageRecord.CommonName order by JobUsageRecord.CommonName
JobsByUserforSite   select JobUsageRecord.CommonName as UserName, sum( JobUsageRecord.Njobs) from JobUsageRecord where (? <= JobUsageRecord.EndTime and JobUsageRecord.EndTime <= ?) and JobUsageRecord.SiteName = ? group by JobUsageRecord.CommonName order by JobUsageRecord.CommonName
JobsByUserforVO   select JobUsageRecord.CommonName as UserName, sum( JobUsageRecord.Njobs) from JobUsageRecord where (? <= JobUsageRecord.EndTime and JobUsageRecord.EndTime <= ?) and JobUsageRecord.VOName = ? group by JobUsageRecord.CommonName order by JobUsageRecord.CommonName
JobsByVO   select JobUsageRecord.VOName, sum(JobUsageRecord.Njobs) from JobUsageRecord where (? <= JobUsageRecord.!EndTime and JobUsageRecord.EndTime <= ?) group by JobUsageRecord.VOName order by JobUsageRecord.VOName JobsByUserforVO -Y
ProbeStatus   call ProbeStatus(?,?,?,?,?) ProbeStatus -L
DailyJobsByProbe -Y
UsageByFacility   call UsageByFacility(?,?,?,?) UsageByProbe -Y
UsageByProbe   select CEProbes.ProbeName, sum( JobUsageRecord.WallDuration) from CETable, CEProbes, JobUsageRecord where (? = 'All' or CETable.facility_name = ?) and CEProbes.facility_id = CETable.facility_id and JobUsageRecord.ProbeName = CEProbes.ProbeName and (? <= JobUsageRecord.EndTime and JobUsageRecord.EndTime <= ?) group by CEProbes.ProbeName order by CEProbes.ProbeName
UsageBySite   select JobUsageRecord.SiteName, sum( JobUsageRecord.WallDuration), sum( JobUsageRecord.CpuUserDuration+ JobUsageRecord.CpuSystemDuration) from JobUsageRecord where (? <= JobUsageRecord.EndTime and JobUsageRecord.EndTime <= ?) group by JobUsageRecord.SiteName order by JobUsageRecord.!SiteName UsageByUser -Y
UsageByUser   select JobUsageRecord.!CommonName as UserName, sum( JobUsageRecord.WallDuration), sum( JobUsageRecord.CpuUserDuration+ JobUsageRecord.CpuSystemDuration), JobUsageRecord.VOName as VOName, JobUsageRecord.SiteName as SiteName from JobUsageRecord where (? <= JobUsageRecord.!EndTime and JobUsageRecord.EndTime <= ?) and (VOName = ? or (? = 'NoVO' and (VOName like '%' or VOName is null))) and (SiteName = ? or (? = 'NoSite' and (SiteName like '%' or SiteName is null))) group by JobUsageRecord.CommonName order by JobUsageRecord.CommonName
UsageByVO   select JobUsageRecord.VOName, sum( JobUsageRecord.WallDuration), sum( JobUsageRecord.CpuUserDuration+ JobUsageRecord.CpuSystemDuration) from JobUsageRecord where (? <= JobUsageRecord.EndTime and JobUsageRecord.EndTime <= ?) group by JobUsageRecord.VOName order by JobUsageRecord.VOName UsageByUser -Y
UsageReport   select JobUsageRecord.JobName, CETable.facility_name, CEProbes.ProbeName, JobUsageRecord.StartTime, JobUsageRecord.EndTime, JobUsageRecord.JobNameDescription from JobUsageRecord, CETable, CEProbes where CEProbes.facility_id = CETable.facility_id and JobUsageRecord.ProbeName = CEProbes.ProbeName and (? <= JobUsageRecord.EndTime and JobUsageRecord.EndTime <= ?) and (? = 'All' or JobUsageRecord.!ProbeName = ?) and (? = 'All' or CETable.facility_name = ?)

Process Accounting Reports
DailyUsageByGroup-pacct   select V.VOName, C.facility_name, Date( V.EndTime) as EndTime, sum( V.CpuUserDuration+ V.CpuSystemDuration) from VOProbeSummary V, CETable C, CEProbes P where V.ProbeName = P.ProbeName and P.facility_id = C.facility_id and C.facility_name =? and ( EndTime >= ? and EndTime <= ?) group by date_format( V.EndTime, ?), V.VOName order by date_format( V.EndTime, ?) DailyUsageByGroup-pacct -Y
DailyUsageByGroup-pacct -L
DailyUsageByGroup1-pacct   select JobUsageRecord.VOName, JobUsageRecord.ProbeName, Date( JobUsageRecord.EndTime) as EndTime, sum( JobUsageRecord.CpuUserDuration+ JobUsageRecord.CpuSystemDuration) from JobUsageRecord where (? = 'All' or JobUsageRecord.ProbeName =?) and (? <= EndTime and EndTime <= ?) group by date_format( EndTime, ?), JobUsageRecord.VOName order by JobUsageRecord.EndTime DailyUsageByGroup1-pacct -Y
DailyUsageByGroup2-pacct   select J.VOName as voname, Date( J.EndTime) as EndTime, sum( J.CpuUserDuration+ J.CpuSystemDuration) from VOProbeSummary J, CEProbes P, CETable C where J.ProbeName = P.ProbeName and P.facility_id = C.Facility_id and C.facility_name = ? and (? <= EndTime and EndTime <= ?) group by date_format(EndTime, ?), J.VOName order by J.EndTime DailyUsageByGroup2a-pacct -X
DailyUsageByGroup2a-pacct   select J.VOName as voname, Date( J.EndTime) as EndTime, sum( J.CpuUserDuration+ J.CpuSystemDuration) from VOProbeSummary J, CEProbes P, CETable C where J.ProbeName = P.ProbeName and P.facility_id = C.Facility_id and C.facility_name = ? and (? <= EndTime and EndTime <= ?) and J.VOName = ? group by date_format(EndTime, ?), J.VOName order by J.EndTime DailyUsageByGroup-pacct -L
DailyUsageByGroup2a-pacct -X
DailyUsage-pacct   select J.ProbeName, J.EndTime as EndTime,

sum(( J.CpuUserDuration+ J.CpuSystemDuration)) as cputime, sum(( J.CpuUserDuration+ J.CpuSystemDuration)* CpuInfo.BenchmarkScore/1000) as normcpu FROM JobUsageRecord J, CpuInfo CpuInfo where J.HostDescription= CpuInfo.NodeName and J.ProbeName = ? and (? <= J.EndTime and J.EndTime <= ?) group by date_format( J.EndTime, ?) order by J.EndTime

 DailyJobsByProbe -L
DailyUsage-pacct -Y1
DailyUsage-pacct -Y2
DailyUsage-pacct-usersys   select J.ProbeName, sum( J.CpuUserDuration) as usercpu, sum( J.CpuSystemDuration) as syscpu, J.EndTime as EndTime,

sum(( J.CpuUserDuration+ J.CpuSystemDuration)) as cputotal, sum(( J.CpuUserDuration+ J.CpuSystemDuration)* CpuInfo.BenchmarkScore/1000) as normcputotal FROM JobUsageRecord J, CpuInfo CpuInfo where J.HostDescription= CpuInfo.!NodeName and J.ProbeName = ? and (? <= EndTime and EndTime <= ?) group by date_format( J.EndTime, ?) order by J.EndTime

 DailyJobsByProbe -L
DailyUsage-pacct-usersys -X
DailyUsage-pacct-usersys -X
DailyUsage1-pacct   select C.facility_name,sum( J.CpuUserDuration) as usercpu, sum( J.CpuSystemDuration) as syscpu, J.EndTime as EndTime,sum(( J.CpuUserDuration+ J.CpuSystemDuration)) as cputotal, sum(( J.CpuUserDuration+ J.CpuSystemDuration)*CpuInfo.BenchmarkScore/1000) as normcputotal FROM JobUsageRecord J, CpuInfo CpuInfo, CETable C, CEProbes P where J.HostDescription=!CpuInfo.NodeName and J.ProbeName = P.ProbeName and P.facility_id = C.facility_id and C.facility_name = ? and (? <= EndTime and EndTime <= ?) group by date_format( J.EndTime, ?) order by J.EndTime DailyJobsByProbe -L
DailyUsage1-pacct -X
DailyUsage1-pacct -X
DateSummary1-pacct   SELECT sum( J.CpuUserDuration+ J.CpuSystemDuration)/(3600*1000) as cpuabs, date_format( J.EndTime, "%Y-%m") as yearmonth, J.VOName as voname, sum( J.CpuUserDuration+ J.CpuSystemDuration) *C.!BenchmarkScore/(3600*1000) as cpunorm FROM JobUsageRecord J, CpuInfo C, CETable CE, CEProbes P where J.HostDescription = C.!NodeName and J.EndTime >= ? and J.EndTime < '2010-12-31' and J.ProbeName = P.ProbeName and P.facility_id = CE.facility_id and facility_name = ? group by yearmonth, voname order by yearmonth DateSummary1a -Y
DateSummary2-pacct   SELECT facility_name, sum( N.CpuUserTime+ NN.CpuSystemTime)/3600*( N.BenchmarkScore/1000) as cpunorm, sum( N.BenchmarkScore /1000 * N.CpuCount*24 ) as cpuavail, date_format( N.EndTime, "%Y-%m") as monthyear, N.CpuCount as CpuCount, N.DaysInMonth as DaysInMonth, N.BenchmarkScore as BenchmarkScore, N.Node as node FROM NodeSummary N, CETable C, CEProbes P where N.EndTime >= ? and N.EndTime >= '2006-04-01' and N.EndTime < '2012-12-31' and N.ProbeName = P.ProbeName and P.facility_id = C.facility_id and facility_name = ? group by monthyear order by monthyear
MonthlySummary1-pacct   select distinct facility_name from CETable C, CEProbes where C.facility_id = CEProbes.facility_id and CEProbes.!ProbeName like "psacct%"

OSG Daily Reports
DailyUsageBySite-osgdaily   select JobUsageRecord.SiteName,
Date( JobUsageRecord.EndTime) as endtime,
sum( JobUsageRecord.WallDuration),
sum( JobUsageRecord.CpuUserDuration+ JobUsageRecord.CpuSystemDuration)
from JobUsageRecord
where EndTime >= ? and EndTime &lt;= ?
group by date_format( JobUsageRecord.EndTime,
?), JobUsageRecord.SiteName
order by date_format( JobUsageRecord.EndTime,?)		 DailyJobsByProbe -L
DailyUsageBySite -X
DailyUsageByVO-daily   select V.VOName as voname,
Date( V.EndTime) as endtime,
sum( V.WallDuration) as wallduration,
sum( V.CpuUserDuration+ V.CpuSystemDuration)as systemduration
from VOProbeSummary V
where (? &lt;= V.EndTime and V.EndTime &lt;= ?)
group by date_format( V.EndTime, ?), V.VOName
order by date_format( V.EndTime, ?)		 DailyJobsByProbe -L
DailyUsageByVO -X
JobsBySite-osgdaily   select JobUsageRecord.SiteName, sum( JobUsageRecord.Njobs)
from JobUsageRecord
where (? &lt;= JobUsageRecord.EndTime and JobUsageRecord.EndTime &lt;= ?)
group by JobUsageRecord.SiteName
order by JobUsageRecord.SiteName		 JobsByUser -Y
JobsByVO-osgdaily   select JobUsageRecord.VOName, sum( JobUsageRecord.Njobs)
from JobUsageRecord
where (? &lt;= JobUsageRecord.EndTime and JobUsageRecord.EndTime &lt;= ?)
group by JobUsageRecord.VOName
order by JobUsageRecord.VOName		 JobsByUserforVO -Y
UsageBySite-osgdaily   select JobUsageRecord.SiteName,
sum( JobUsageRecord.WallDuration),
sum( JobUsageRecord.CpuUserDuration+ JobUsageRecord.CpuSystemDuration)
from JobUsageRecord
where (? &lt;= JobUsageRecord.EndTime and JobUsageRecord.EndTime &lt;= ?)
group by JobUsageRecord.SiteName
order by JobUsageRecord.SiteName		 UsageByUser -Y
UsageByVO-osgdaily   select JobUsageRecord.VOName,
sum( JobUsageRecord.WallDuration),
sum( JobUsageRecord.CpuUserDuration+ JobUsageRecord.CpuSystemDuration)
from JobUsageRecord
where (? &lt;= JobUsageRecord.EndTime and JobUsageRecord.EndTime &lt;= ?)
group by JobUsageRecord.VOName
order by JobUsageRecord.VOName		 UsageByUser -Y
-- JeffreyMack - 12 April 2007
Edit | Attach | Print version | History: r7 < r6 < r5 < r4 < r3 | Backlinks | Raw View | More topic actions
Topic revision: r7 - 11 Dec 2008 - 18:51:44 - KyleGross
Log In or Register

Google Custom Search
Common links

Accounting

Meta-TWiki links

 
Powered by TWiki
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback