Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

swql time summarisation/aggregation in 30 minutes sample rate

i have historical values , and i am tryign to write swql , which will sumarrise values in 30 minutes intervals.. i know how to do it hourly, daily etc, but this time i need to have it per 30 minutes.

i am not including any swql, as i have no idea how to summarise in swql time to 30 minutes samples.

can you help me please with some example ?

Find more posts tagged with

Accepted answers

mraky

ok, so here is final version of summarisation values in SWQL..

```

select top 50

no.Caption,

avg(no.Traffic.PercentUtil) as util ,

(

AddMillisecond(-MilliSecond(no.Traffic.DateTime),

addSecond(-Second(no.Traffic.DateTime),

addMinute(

-Minute(no.Traffic.DateTime)+((Minute(no.Traffic.DateTime))/30)*30

,no.Traffic.DateTime

)

as date

from Orion.NPM.Interfaces no,

Orion.engines

where

no.Caption like '%ELEMENT%' and

no.Traffic.DateTime >Addminute(-50, KeepAlive)

group by

no.Caption,

(

AddMillisecond(-MilliSecond(no.Traffic.DateTime),

addSecond(-Second(no.Traffic.DateTime),

addMinute(

-Minute(no.Traffic.DateTime)+((Minute(no.Traffic.DateTime))/30)*30

,no.Traffic.DateTime

)

```

All comments

m_roberts

Hi,

You need to adjust the statement to formulate the date value including the GROUP BY. I have provided an example using SQL (not tested for SWQL) to highlight the changes. The following is the default Average and Peak Traffic Rate - Last 7 Days report, with 30 minute summarisation. Note the content 'Float)*48)/48,0)' is what has been changed to perform the summarisation and you will need to adjust the WHERE clause to not reference a hard set SQL base time. If you wanted it to be 45 minute summarisation the numbers to use would be 32:

SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)*48)/48,0) AS SummaryDateTime,Nodes.NodeID AS NodeID,Interfaces.InterfaceID AS InterfaceID,Nodes.Caption AS NodeName,Nodes.VendorIcon AS Vendor_Icon,Interfaces.Caption AS Interface_Caption,Interfaces.InterfaceIcon AS Interface_Icon,AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bpsFROM(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)WHERE( DateTime BETWEEN 42484 AND 42491.5833333333 )GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)*48)/48,0),Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIconORDER BY SummaryDateTime ASC, 4 ASC, 6 ASC

Mark Roberts

Prosperon - UK SolarWinds Partners

Installation | Consultancy | Training | Licenses

mraky

Hello,

first thx alot for very fast answer... unfortunately i have no issue to do it in oracle/mssql sql.... your example doesnt work in swql studio... my problem is to do it in SWQL...

BUT, i succeed just few minutes after i posted answer. (and belive me, i was looking for answer quite long before i posted question)

here is example

select top 50

no.Caption,

avg(no.Traffic.PercentUtil) as util ,

(

ToString(Day(no.Traffic.DateTime))+

ToString(Hour(no.Traffic.DateTime))+

ToString(((Minute(no.Traffic.DateTime))/30)*30)

) as date

from Orion.NPM.Interfaces no,

Orion.engines

where

no.Caption like '%MUSTANSIRYAH RNC SWITCH A%' and

no.Traffic.DateTime >Addminute(-50, KeepAlive)

group by

no.Caption,

(

ToString(Day(no.Traffic.DateTime))+

ToString(Hour(no.Traffic.DateTime))+

ToString(((Minute(no.Traffic.DateTime))/30)*30)

)

mraky

ok, so here is final version of summarisation values in SWQL..

```

select top 50

no.Caption,

avg(no.Traffic.PercentUtil) as util ,

(

AddMillisecond(-MilliSecond(no.Traffic.DateTime),

addSecond(-Second(no.Traffic.DateTime),

addMinute(

-Minute(no.Traffic.DateTime)+((Minute(no.Traffic.DateTime))/30)*30

,no.Traffic.DateTime

)

as date

from Orion.NPM.Interfaces no,

Orion.engines

where

no.Caption like '%ELEMENT%' and

no.Traffic.DateTime >Addminute(-50, KeepAlive)

group by

no.Caption,

(

AddMillisecond(-MilliSecond(no.Traffic.DateTime),

addSecond(-Second(no.Traffic.DateTime),

addMinute(

-Minute(no.Traffic.DateTime)+((Minute(no.Traffic.DateTime))/30)*30

,no.Traffic.DateTime

)

```