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

I need to generate a report that sorts the result using multiple conditions.

fazl-e-azeem

The report is of interface utilization, and the sorting need to be done on following conditions.

1. Sort the report w.r.t Monday-Friday(Sat and Sun excluded)

2. Sort the report w.r.t business hours i.e 9am -5pm

3. Sort the result on monthly basis.

All need to be in same report, which includes only business days and business hours and on monthly basis.

I really need help with this. Will be much obliged to get the help.

Below is a SQL query that i received from someone, but i do not know how it works or what will be the result because the query is generating error.

declare @periodbegin datetime

set @periodbegin=';17-05-2014'

declare @periodEnd datetime

set @periodEnd=';18-05-2014'

SELECT

StartTime.EventTime,

Nodes.Caption,

StartTime.Message,

DATEDIFF(Mi, StartTime.EventTime,

(SELECT TOP 1

EventTime

FROM Events AS Endtime

WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5

AND EndTime.NetObjectType = 'N'

AND EndTime.NetworkNode = StartTime.NetworkNode

ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes,

DATEDIFF(Mi, StartTime.EventTime,

(SELECT TOP 1

EventTime

FROM Events AS Endtime

WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 14

AND EndTime.NetObjectType = 'N'

AND EndTime.NetworkNode = StartTime.NetworkNode

ORDER BY EndTime.EventTime)) AS OutageDurationInMinutesByPower

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND

eventtime between ('17/07/2014' and '18/07/2014')

ORDER BY Nodes.Caption ASC

Best Regards.

Find more posts tagged with

Accepted answers

pparsaie

fazl-e-azeem

In the spirit of "tons of ways to do this" here is what might be an easier way. I'm not sure what you're the most comfortable with, but for me most of the time I use SWQL as its more "flexible" for me. Using the GUI you can create a report using a custom table, then the dynamic query builder to do something like what follows:

Here you add the last month, this month portion of your query:

Select your columns and then change it up to give you what you need.

This was just a quick report thrown together without a real understanding of what you're really trying to show. For me I'd make sure there was a node name for the device that had the interfaces grouped under it, if you're looking for a report on interface utilization for all interfaces on a device by device basis. In my environment that wouldn't be that important and I'd want to report on interfaces that were pertinent to whatever it is I'm reporting but this should help get you going on your report.

All comments

jose.vazquez

Hi man, did you try creating a new report? if you créate a new report you can select the period.

fazl-e-azeem

the thing is, solarwinds TAC was engaged, he said there are ways to generate report, either on monthly basis, or on 7 daya basis, or on business hours basis, but all those reports are different report.

What my requirement is that i want all those sorting applied in 1 report which will be on monthly basis and only shows the data of working days of monday-friday and from 9am-5pm, this is the report i want.

fazl-e-azeem

wabbott, CourtesyIT, aLTeReGo, patrick.hubbard, webbster61, byrona, jeremymayfield, silverbacksays, Jfrazier, jbiggley, familyofcrowes, mrs.alterego

Guys, can you help me out with this one? Solarwinds support has denied any help due to the issue will be resolved from SQL Query and solarwinds doesnot provide support for SQL.

Best Regards.

CourtesyIT

fazl-e-azeem,

I am not the best SQL coder but will look into it. It may take me a few days but will see what I can get done.

Thanks,

zackm

Can you throw together a tiny little demo table of what you expect the end result to look like?

I'm having a hard time visualizing this one in my head. I think it can be done, but I hesitate to spend a lot of time on an assumption of what you want only to find out you were looking for something else

Let me know what exactly you're looking for and I'll see what I can come up with for you.

fazl-e-azeem

Hi Eric,

Thanks for the response, this report is a real pain. Would appreciate any sort of help.

Thanks.

fazl-e-azeem

ok zackm, i will try to give it a try to come up with something that is close enough so that you and anyone who has a confusion would understand.

Thanks.

pparsaie

Using SWQL I usually use code that is something like what's shown below to do what you're looking for.

WHERE    Month(ToLocal(DateTimeUTC)) = Month(GetDate()) -1AND    daydiff(datetrunc('week',ToLocal(DateTimeUTC)), ToLocal(DateTimeUTC)) +1 in (1,2,3,4,5)AND    hour(ToLocal(DateTimeUTC)) >= 6AND    hour(ToLocal(DateTimeUTC)) <= 17

I'm going off memory and might be off but it looks close to what you're asking for without using the names in the SWQL table you're using.

The above should return results from last month, m-f between 0600 and 1700 local server time (SolarWinds server time). Its the where portion that would do the time/date portion of what you're asking.

If you have tons of data like I do in my DB you'd also want to make sure the year is included. To pull for the current month data you'd remove the -1 at the end of the month line (line 2).

There may be a smoother way of doing this but its always worked for me.

If you don't know SWQL or SQL you could build the report in the web report section and just use the drop down to help do the same.

You can do a two step aproach as shown below or filter off the columns in the report editor. There is a ton of ways to do what you're looking to do.

fazl-e-azeem

Hi pparsaie,

Thanks for your response. Can you kindly share the report output u get from this query? the screenshot you provided, it only shows the result for past 30 days, i want sorting on 3 levels, no1.last 30 days, no.2 only business days like mon-fri and no.3 only business hours i.e 9-5 office hours.

Thanks.

pparsaie

fazl-e-azeem

Here you add the last month, this month portion of your query:

Select your columns and then change it up to give you what you need.

fazl-e-azeem

Thanks pparsaie, that looks like that should do the trick, but 1 thing i want to know is, when i am going to select the timestamp, in category it is showing different parameters, what i want to select is for node availability, but all options i can see are for either interfaces or applications, this matters because when i am going to select the option of total duration in minutes on table layout, that option is showing in grey color, im unable to add that resource.

abdhijasharma

Hey pparsaie, thanks a lot for the solution, helps me a lot!! Just one question though, in the same report. How to summarize the results so that I get a single row to display the utilization for each interface, instead of multiple values? Hope I could clearly state my query.

Thanks in advance!!

fazl-e-azeem

I want to generate report on Node downtime which would show total duration of downtime in minutes. I am unable to find timestamp for node downtime/availability, i am only getting options related to interfaces/application but not node.