abel5405 Absent Member.
Absent Member.
1941 views

Help with a Query to SUM values.

Greetings All,
I am pulling some F5 stats using the SNMP module and I need to SUM some values but I just can't seem to get it right. The issue I have is that all the data comes in on slightly different time stamps so doing my summation through group fails. Below is my query. Any help would be greatly appreciated.

v/r
Chris



________________________________________________________________

--Get UTC Time
declare @UTCtd AS datetime
set @UTCtd = getutcdate()

--Get Today's Date and Time
declare @TodayTD AS datetime
set @TodayTD = GETDATE()

--Get Time Zone Informaiton
declare @TimeDif as int
set @TimeDif = DATEDIFF(hh, getutcdate(), getdate())

CREATE Table #SumF5Connections (DataSource VARCHAR(9),Value INT,DateLastVal DATETIME,DataAgeMinutes INT)

insert into #SumF5Connections

select
'ESOC West' as DataSource,
sum(d.Value) as Value,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (d.Time)) as DateLastVal,
DATEDIFF(mi, @TodayTD, (DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (d.Time)))) as DataAgeMinutes

from
QDB1.dbo.DataHeader as h WITH (NOLOCK)
join QDB1.dbo.Data as d WITH (NOLOCK) on h.DataID = d.DataID
where
h.Status & 0x08000000 = 0
and
h.KPName like '%F5_SNMP - ltmVirtualServStatClientCurConns'
and
h.HiddenLegend like '%Test_VIP%'
and
h.HiddenLegend not like '%Test_VIP_80%'

group by d.time

union

select
'ESOC East' as DataSource,
sum(d.Value) as Value,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (d.Time)) as DateLastVal,
DATEDIFF(mi, @TodayTD, (DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (d.Time)))) as DataAgeMinutes

from
QDB2.dbo.DataHeader as h WITH (NOLOCK)
join QDB2.dbo.Data as d WITH (NOLOCK) on h.DataID = d.DataID
where
h.Status & 0x08000000 = 0
and
h.KPName like '%F5_SNMP - ltmVirtualServStatClientCurConns'
and
h.HiddenLegend like '%Test_VIP_443%'

group by d.time

order by DataAgeMinutes desc

select
Sum(Value) as Value,
DataAgeMinutes,
Replace(DateLastVal, '.000', ' ') as DateLastVal
from
#SumF5Connections

Group By DataAgeMinutes,Replace(DateLastVal, '.000', ' ')

order by DataAgeMinutes DESC

drop table #SumF5Connections
Tags (1)
0 Likes
1 Reply
AutomaticReply Absent Member.
Absent Member.

Re: Help with a Query to SUM values.

abel5405,

It appears that in the past few days you have not received a response to your
posting. That concerns us, and has triggered this automated reply.

These forums are peer-to-peer, best effort, volunteer run and that if your issue
is urgent or not getting a response, you might try one of the following options:

- Visit https://www.microfocus.com/support-and-services and search the knowledgebase and/or check
all the other self support options and support programs available.
- Open a service request: https://www.microfocus.com/support
- You could also try posting your message again. Make sure it is posted in the
correct newsgroup. (http://forums.microfocus.com)
- You might consider hiring a local partner to assist you.
https://www.partnernetprogram.com/partnerfinder/find.html

Be sure to read the forum FAQ about what to expect in the way of responses:
http://forums.microfocus.com/faq.php

Sometimes this automatic posting will alert someone that can respond.

If this is a reply to a duplicate posting or otherwise posted in error, please
ignore and accept our apologies and rest assured we will issue a stern reprimand
to our posting bot.

Good luck!

Your Micro Focus Forums Team
http://forums.microfocus.com



0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.