Report - Data interval every 15mins

How can I create a swql/sql report that shows data in 15 mins interval?
I can create this report on a chart but I need it to be replicated in a web report/report writer

Find more posts tagged with

swql report

sql

report_writer

SWQL

orion reports

custom report

sql_report

Accepted answers

mesverrum

So you would use the average() and max() functions as normal, but in the group by section you include your downsample. Something like

select top 100
     downsample(cpu.DateTime ,'00:15:00') as [15m_Block]
   , cpu.nodeid
   , avg( cpu.AvgLoad ) as [Average]
   , max( cpu.AvgLoad ) as [max]

from orion.CPULoad cpu
group by cpu.NodeID
       , downsample(cpu.DateTime,'00:15:00')

All comments

KMSigma.SWI

You can use the DOWNSAMPLE function. (under "Date/Time" functions.)

mykopascua

Hi. I’ve already used the downsample and this is exactly what Im looking for, although I also need to get the avg/max every 15mins of my data as well

mesverrum

So you would use the average() and max() functions as normal, but in the group by section you include your downsample. Something like

select top 100
     downsample(cpu.DateTime ,'00:15:00') as [15m_Block]
   , cpu.nodeid
   , avg( cpu.AvgLoad ) as [Average]
   , max( cpu.AvgLoad ) as [max]

from orion.CPULoad cpu
group by cpu.NodeID
       , downsample(cpu.DateTime,'00:15:00')

mykopascua

Exactly what I needed. Thanks for the big help!