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

Business hours reporting in the Web Based Report Writer

As many folks have found, there is not currently a native method in the web report writer to allow for business hours reporting.

This does not mean however that it cannot be done.

In order to achieve a custom table with business hours filters, we must first start in the legacy Orion Report Writer.

Your business hours report can begin as any standard report.  The process of filtering to business hours involves adding 4 filters.

- Day of week greater or equal to Monday

- Day of week less or equal to Friday

- Hour of day greater or equal to 7

- Hour of day less or equal to 18

In my example, I am assuming business hours to be 7AM to 7PM Monday thru Friday.

Once you have applied the filter you will find you can produce the report in Report Writer quite easily.

The process of converting this report into a Web Based report is very simple.

First - use the Show SQL option on the Report Menu within Report Writer.

Select the entire SQL statement and copy it.

For the report timeframe, you will notice that report writer uses a date math shortcut of converting the 'last x amount of time' (last 7 days for example) to a statement like this:

( DateTime BETWEEN 42472 AND 42479.75 )

As you might guess, this report will ALWAYS and ONLY be for those specific decimal dates, so we need to correct this in order for our report to work in the Web Based Report Writer as we intend it to.

We will replace this line with the following:

( DateTime BETWEEN (DateAdd(dd, -7, GetDate()) AND GetDate())

This statement ensures that our report always runs for the intended time frame, but looking at a delta from today's date in order to determine last 7 days.  You can modify the DateAdd parameters in order to get different time frames.  See DATEADD (Transact-SQL) for more details on working with the DateAdd function.

Beyond that, no other changes are necessary, our query can be copied and pasted into the Custom SQL UI in the Web Based Report Writer and you can then configure your report columns as desired to achieve the finished result.

Here is a complete query which returns "Top 10 Interfaces by Transmit bps for the last 7 days, during business hours."

SELECT TOP 10
Nodes.NodeID AS NodeID,
Interfaces.InterfaceID AS InterfaceID,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)


WHERE
( DateTime BETWEEN (DateAdd(dd,-7,GetDate())) AND GetDate())
AND 
(
  (DATEPART(weekday, DateTime) >= 2) AND
  (DATEPART(weekday, DateTime) <= 6) AND
  (DatePart(Hour,DateTime) >= 7) AND
  (DatePart(Hour,DateTime) <= 18)
)


GROUP BY Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon


ORDER BY 8 DESC

Happy business hours filtering!

Comments

Bill, reminder to add path for icon.

Thanks Jennifer for the reminder! 

For those of you that participated or have watched the webinar, there is a step in the Web Based Custom SQL report where you can format the columns to include the icons that are a part of the SQL query.  For the Vendor there is a built in drop down to select, but this isn't true for the interface type.  Instead you will simply select 'generic icon' and then provide the correct path.  On all standard installations that path is '/NetPerfMon/images/Interfaces/{0}.gif'  leave the {0} variable so that the system will automatically provide the correct numeric interface type identifier.

If you need help - you know where to ask!

NPM v12 added native support to define Business Hours reports

pastedImage_0.png

Can you tell me where this timestamp would be located? I did a search and it took forever. HolyGuacamole

I think I found it under component, does it matter which one I use?

The procedure is the same. It depends on what your Data source is going to be. If you want a Node level report, then Node is what you choose. If you want an Interface level report, then Interface is what you choose.

wow, was this ever made a note of anywhere else HolyGuacamole​ ? This is pretty huge.

I would expected to see it in the NPM 12 release notes but I am not seeing it.

I am also having problems locating the Timestamp field.  Also, I wanted to know if this allows us to exclude (or include) certain days of the week.  For example, we want our utilization reports to be from 7 am to 7 pm but only Monday - Friday, since our offices are closed during weekends. 


Can you enlighten me on the above?  Thanks! 

Not all heroes wear capes. You deserve a medal.

under "response time history" the date option is the variable you're looking for.

Did this feature morph or disappear? I'm trying everything I can see to create the simple if/then statements shown above, and regardless of which Timestamp I choose (dozens show up in the search), the values offered after "time of day is after" never include an actual time. 

So my If statement ends up looking like:

Timestamp :: time of day is after :: Last Boot

I can replace "Last Boot" with 7 different options, none of which are an actual time.

Version history
Revision #:
1 of 1
Last update:
‎04-21-2016 05:38 PM
Updated by: