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

95th Percentile Circuit Report based on local site business hours

This report is written to show 95th percentile data during local site business hours (8am to 6pm) Monday thru Friday.   Both the hours and the days can easily be modified based on your needs.  It uses custom properties based off of GMT time.  I have included a short readme file and a screen shot example of the interface custom properties I used to build the report.

 

Enjoy!

Labels (1)
Attachments
Comments
The first upload had a problem with monthbegin function. I have since corrected so please re-download the zip if you ran into an error while trying to install that function into db.
Oops, you are right. It was a typo on my part for the FunctionCode property. Thanks!
That error would occur when you haven't created all of the Custom Properites I listed in the readme. I also gave a screenshot of all the properties. Once you create those and populate the ones you want on the repot that error should go away
Invalid column name FunctionCode when trying to install/run 95th_percentile_report_-_Last_month. Any ideas? TIA
First of all, I have to apologize for lack of my knowledge of Solarwinds Report. I'm new to Solarwinds. I have downloaded your useful reports to the current solarwinds server Version 8.5.1. And it only shows Burst and CIR value. Can you guess what I had done wrong?

Thanks in advance.
Just want to add that I could NOT open 95th_Percentile_Report_-_Last_Month.OrionReport on Report Writer. Also, when I srtipped out the beginning and ending metadata looking part on monthbegin.OrionReport, and executed that using Report Writer custom SQL, it gave back an error saying that that object already exists in the database. I followed all the directions in the README, thanks so much, just curious what I may be doing wrong. Thanks.
Hi and thanks so much for sharing this with the community. I am running Orion v10 SP1, trying to follow the directions. I wonder if there has been a change in the Report Writer that is affecting the functions because when I try to open them using Report Writer to execute them, the Report Writer application tells me that it cannot open those files. Also, when I cut and paste the code into a Custom SQL report and click Execute, it gives me an error saying "SQL Error: Incorrect syntax near '<' ", which is the first character of the file (as seen in Notepad). When i cut and paste this code into the Solarwinds Database Manager, I also get an error. So basically not able to execute the functions, so I am curious if its something with version 10 that has changed or what I may be doing wrong (am I supposed to cut out parts of the code on those functions? for example?). Thanks.
Is this report changed as weekly instead of monthly report? If it is, which vaules in the SQL statements have to be changed ??

TIA
This looks like a good report even though implementation is not simple.
What are consequences of keeping all interfaces In EST? and allowing the EDT to occur automatically. My network exists completely in eastern timezone so it seems pointless to introduce the timezone change to GMT.
To retain local time, would I need to compensate elsewhere in the SQL?
Thank you so much for this content. We've been struggling with this for some time now.
I too would love to be able to view a weekly version of this report. Or have the ability to specifiy, Last 1 week last, 2 weeks or last 3 weeks. Does any one know how I could modify the functions to allow for this.

Thanks again !
Hello again,

I did some testing of this report and found that it definitely runs for business hours (as specified by the User when installing the functions) but it is also running 7 days a week, not excluding weekends. Anyone have any idea why this might be happening? Could it be something that I neglected when installing the functions?
The only detail I changed in the 'isbusiness' function was the DATEPPART referring to the hour of the day the report will run for and this part runs fine. Like I said It's not excluding any days of the week and I have conclusively tested this. Any help would be great?
Ok so following my previous post I discovered that there is one line in the 'isbusiness' function that needs to be changed in order for the report to run Mon-Fri only.

Find the line :
SET @theDay = DATEPART(day, @adjDate)
and change to:
SET @theDay = DATEPART(weekday, @adjDate)

Without this line change the report runs for Mon -Sun. With the line change the report runs Mon - Fri

HTH
Ciaran
Hi, I am having the same problem as goofyzip from 11/16/2010 above. I am also on Orion v10. Does this work with v10?
It does work with V10. I found other entries that said not to use Notepad/Wordpad for the SQL commands and that solved my problem. Also note that there is a correction to the day of the week function code listed in other entries.
How do you set the NPM Pollers to GMT?
Not being a SQL wizard at all, I'm having a brutal time trying to get these functions into my DB. I have no idea how to clean up the syntax to get the functions in. Really looking forward to using this, but I'm struggling. Anybody got any ideas?
NPM 10.20.1, SQL 2008 R2
Thanks, Dan

Hi and thanks for your sharing. I follow your instruction and run the report. But when I run the report, there comes a error message "SQL Error: Sort order cannot be applied." Do you know how to fix it?

Thanks.

If I run the report by "Report Writer", there comes the error message. But if I run the report by Web UI "Manage Report", it works well.

Hi Ciaran,

Thanks for your post.

Have you verified the time range?

For example, the following SQL query runs well, I can get what I want, and the time range is last month.

"dbo.in95th(Nodes.Caption, Interfaces.CIR, dbo.monthBegin(1), dbo.monthEnd(1)) / (Interfaces.CIR * 1048576) as in95th"

But when I change the function from dbo.monthBegin/End(2) to dbo.monthBegin/End(2) to get the month before last month data, it returns NULL.

Do you know why?

Thanks.

Hello guys.

I have run this report. Our SQL and NPM servers are in the EST time. So custom property was populated with off of EST time values.

Should this report work with non GMT time zone? I hope the answer is yes. I am asking just to be on the safe side in case if there is anything what I miss.

Is there a way to display the summary (start date - end date) of the calculated values? I mean, the first and last date that the interface receives data and not from the date we set up? Thanks.

Version history
Revision #:
1 of 1
Last update:
‎01-15-2010 12:00 AM
Updated by: