cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 11

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

Jump to solution

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.

0 Kudos
1 Solution

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:

Capture.PNG

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

Capture2.PNG

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

Capture3.PNG

Capture4.PNG

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.

View solution in original post

18 Replies
Level 10

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()) -1

AND

    daydiff(datetrunc('week',ToLocal(DateTimeUTC)), ToLocal(DateTimeUTC)) +1 in (1,2,3,4,5)

AND

    hour(ToLocal(DateTimeUTC)) >= 6

AND

    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.

pastedImage_1.png

pastedImage_0.png

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.

0 Kudos

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:

Capture.PNG

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

Capture2.PNG

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

Capture3.PNG

Capture4.PNG

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.

View solution in original post

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.

0 Kudos

fazl-e-azeem​,  I use Uptime and Downtime Report for node based reporting.  I get Node Name, Down Time, Message/description, Outage in Minutes, and Uptime.  Look at this one to see if it might help with your nodes request.

0 Kudos

Hi CourtesyIT​,

Thanks for the response, where as the requirement has been forwarded by the client and they want to see the desired result, i have tried many ways to generate their desired report, but no success yet, pparsaie​ gave an excellent direction, but unable to generate the report, unable to select some options to show in the table layout.

0 Kudos

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!!

0 Kudos

Sorry for the late response, which values do you mean? Do you mean show the interface statistics for that time period summarized? 

Would it be: For the entire month, show each interface and summarized info for the entire month?

0 Kudos

Yeah, that is what I wanted. I tried it using the Web based report writer, but couldn't do it, though when I tried the same logic you posted using report writer, it generated the report perfectly. That is why I mostly prefer the report writer.... Thanks for all the help anyway..!!

0 Kudos

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.

0 Kudos

Yeah that's why for me I like to use SWQL as it makes things easier all around.

If you can explain what the output should be I might be able to help.

What would the report look like you are trying to generate?

Level 15

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.

0 Kudos

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.

0 Kudos

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,

0 Kudos

Hi Eric,

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

Thanks.

0 Kudos
Level 11

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.

0 Kudos
Level 9

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

0 Kudos

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.

0 Kudos