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 query with DATETIME and SUM

Hello,

Just getting tired of figuring this out on my own, I am no DB guy.

I am attempting to get Peak VPN stats for a report from 4 ASA's built into a custom UdNP poller. I have the poller built, and can get this out from what I've figured out. As you can probably tell, I've been bouncing back and forth from MSSQL and SWQL. This is semi-working in SWQL, but I need a time period for today's date(current date), and a way to sum the queries from the four ASA's into one result. (And then also, it duplicated to produce two total results, with the second result being CurrentDate - 7 days.) Basically, Peak VPN usage for the current date and second result being Peak VPN usage the same day, a week earlier.

This...

SELECT MAX([RawStatus]) AS MAX_VPN_USERS
FROM
FROM [Orion.NPM.CustomPollerStatistics]
WHERE [CustomPollerAssignmentID] = '29daf78c-906b-4c64-bb8b-1563975527f6' AND DATETIME>=('2020-04-16') AND DATETIME<=('2020-04-17')
UNION
(SELECT MAX([RawStatus]) AS MAX_VPN_USERS
FROM [Orion.NPM.CustomPollerStatistics]
WHERE [CustomPollerAssignmentID] = '5c4be754-32b2-4efd-8e3f-ac06008d1382' AND DATETIME>=('2020-04-16') AND DATETIME<=('2020-04-17'))
UNION
(SELECT MAX([RawStatus]) AS MAX_VPN_USERS
FROM [Orion.NPM.CustomPollerStatistics]
WHERE [CustomPollerAssignmentID] = '0d79826d-9f33-4b97-ad41-34adb169cb23' AND DATETIME>=('2020-04-16') AND DATETIME<=('2020-04-17'))
UNION
(SELECT MAX([RawStatus]) AS MAX_VPN_USERS
FROM [Orion.NPM.CustomPollerStatistics]
WHERE [CustomPollerAssignmentID] = 'b15947fd-87df-46e0-b1ff-36b6b29afbdd' AND DATETIME>=('2020-04-16') AND DATETIME<=('2020-04-17'))

Results in this,

	MAX_VPN_USERS
	2139
	2798
	3007
	3665

which is Peak for the Datetime I have in each query for a period, but I just want that to be the current date(in Eastern Time), if that is a function, and then, of course to sum the four Peaks, which is the Only result I really want.

Any help would be appreciated.

Find more posts tagged with

Accepted answers

mesverrum

This seems like it should work a bit easier to get the peaks

SELECT MAX([RawStatus]) AS MAX_VPN_USERS
FROM [Orion.NPM.CustomPollerStatistics]
WHERE
[CustomPollerAssignmentID] in ('29daf78c-906b-4c64-bb8b-1563975527f6','5c4be754-32b2-4efd-8e3f-ac06008d1382','0d79826d-9f33-4b97-ad41-34adb169cb23','b15947fd-87df-46e0-b1ff-36b6b29afbdd')
AND DATETIME>=ADDDAY(-1,getdate())
AND DATETIME<=getdate()
group by CustomPollerAssignmentID

and for sum of those this should work

select sum(t2.MAX_VPN_USERS) as TOTAL
from
(SELECT MAX([RawStatus]) AS MAX_VPN_USERS
FROM [Orion.NPM.CustomPollerStatistics]
WHERE
[CustomPollerAssignmentID] in ('29daf78c-906b-4c64-bb8b-1563975527f6','5c4be754-32b2-4efd-8e3f-ac06008d1382','0d79826d-9f33-4b97-ad41-34adb169cb23','b15947fd-87df-46e0-b1ff-36b6b29afbdd')
AND DATETIME>=ADDDAY(-1,getdate())
AND DATETIME<=getdate()
group by CustomPollerAssignmentID) t2

All comments

mesverrum

throwawayStat

Ahh, I had come to the conclusion that I was going to have to use GetDate, but wasn't sure how to.

Thank you!

throwawayStat

While I was able to utilize your solution provided, I'm also struggling with trying to assign the (-1,getdate()) as a timestamp column.

It results in:

but I'd like that result to be timestamped(Really, Datestamped, just need the Date) with the date of the result, which is today's date -1 (previous date)

Also, does anyone know where the data that produces this chart would be ? This is the sum that I would like to take a max against, because it's the sum of the 4 ASA's during a particular polling period, instead of the max, per ASA, for the day, which may happen at different times for each ASA based on how the load balancing is working. In the chart building, you can choose a sum, instead of the individual ASA CrasNumSessions. I assume that the data has to be captured somewhere in the DB if it can reference historical sessions? Or is it just referencing the historical sessions by ASA and doing the math?

mesverrum

Its just adding it up on the fly.