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

3.5 SQL code/report question

I have a need to look at NETFLOW IP group apps stats and I want another monitoring app to query the last 15 minutes or something so it can trend the info.

What tables do I need to hit to get that info. I wrote the code for 4+ hours ago *summarized), but want something more close to realtime and it needs to run quicker than hitting ALL of those tables...

In report writer, I can do the last hour, but

Anyone have any thoughts?

SQL:::::::this gives me 5 hours to 4 hours ago.

SELECT TOP 4
IPAddressGroups_Dest_IPAddressGroups.IPAddressGroupName AS IP_Address_Group,
SUM(NetflowSummary.TotalBytes) AS SUM_of_Bytes_Transferred
FROM
NetflowSummary LEFT OUTER JOIN IPAddressGroups IPAddressGroups_Dest_IPAddressGroups ON (NetflowSummary.DestIPSort BETWEEN IPAddressGroups_Dest_IPAddressGroups.IPRangeStart AND IPAddressGroups_Dest_IPAddressGroups.IPRangeEnd)
WHERE
( DateTime between
dateadd (hh, datepart(hour, dateadd(hh, -5,getdate() )) , convert(datetime, floor(convert(float,getdate())), 120 ))
AND
dateadd (hh, datepart(hour, dateadd(hh, -4,getdate() )) , convert(datetime, floor(convert(float,getdate())), 120 ))
)
AND
(
(IPAddressGroups_Dest_IPAddressGroups.IPAddressGroupID IS NOT NULL) AND
(IPAddressGroups_Dest_IPAddressGroups.Enabled = 1)
)
AND
(
(EXISTS(SELECT 1 FROM NetFlowSources WITH(nolock) WHERE NetFlowSources.InterfaceID=InterfaceIDRx AND NetFlowSources.Enabled=1))
)
GROUP BY IPAddressGroups_Dest_IPAddressGroups.IPAddressGroupName
ORDER BY 2 DESC

Find more posts tagged with

Accepted answers

All comments

njoylif

I expect it uses different tables (not summarized) because when I try to copy the top 5 by IP addr group last 24 and change it to last hour, I get the following error:

Hi,

in ReportWriter you are able to query only compressed data, it means data, which are older than your setting Keep uncompressed data for .... .

If your setting is 15 minutes, than you are able query data 15 minutes behind present.

njoylif

bummer, but that's kind of what I figured.

Also, since staff responded, I would love to know how to add an index on this type of "clustered" table environment so I can speed up my queries - if you can assist with that. I think it is already indexed on datetime, I'd like to add index on destinationIP or an IP group number column

Thanks for the reply and any further help is greatly appreciated.

njoylif

another thing that just occurred to me is I have no issue (and probably was going to) running this outside of report writer.

Is there a way to get to that info before compression? my raw time is 4 hours.

Thanks

indexes, hmmm nice, but I can't recommend this to you. We made a lot of testing in our lab, and if you add more indexes you will slow down insert operation proportionately. That's easy to calculate. And in case, that you are not able insert new data to DB, you start dropping and you will have gaps in charts. I'm not sure how chatty your network is, but anyway amount of data is enormous.

And I have to disappoint you, there's no official way how to access uncompressed data in ReportWriter.

njoylif

such is life. If I want it bad enough, I'll keep uncompressed for 15 min as you initially stated rather than 4 hours and just be 15 min delay. I know the SQL Query for that.

Thanks for the responses!