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

How to create custom SLA reports

Something we hear quite often is "how can I build custom SLA report in Orion". Everybody who is a bit familiar with Orion knows our web-reporting tools which is the right place to start, however sometimes it requires more than a click & point tweak in reporting.

(we've made this article official: SLA Reports )

I'll use this SLA request example: WAN SLA availability by time zone & 7x24 in order to demonstrate one of the ways how to get there.

What you'd need to accomplish this?

  1. NPM (or any other SolarWinds product)
  2. Orion SDK - Orion SDK Information
  3. At least one hour data history for the devices which should appear in your SLA report. In my case, Nodes and business hours.
  4. 30 minutes of your time

What will be the result:

Our goal is to create daily custom SLA report for Node availability (in %) which shows the device availability only for our business hours - in our case 7AM - 6PM

The result may look like this:

Node             SLA Availability          SLA Hours         

New York      92.33                         7am-6pm   

Let's get started:

First problem we need to solve is how to "tell" system what are our report business hours. This seems to be a best fit for Orion custom properties. Let's create two custom properties: 1) time_from 2)time_to and assign the values to all nodes.

Go "All Settings" -> "Manage Custom Properties" -> "Add Custom Property" -> Select Nodes and click "next" -> specify property name "time_from" and apply on "reports" -> Select all nodes to assign a property.

pastedImage_9.png

Now we need to assign the start rush our time in a bulk operation to all nodes. Select "view/edit values" and set "7" to the field:

pastedImage_12.png

pastedImage_13.png

pastedImage_14.png

Ok, now go and repeat all steps above for "time_to" and value "18" (6PM).

Ready? Good. We have data ready and now it's time to data-mining. For that purpose we're going to use SolarWidns proprietary business logic and data layer represented by SolarWinds Information Service - SWIS. Which has simimar syntax to SQL but gives you much more power to get properties via "." operator (no need to know entire database table structures). In order to tune the final query we will use SWQL Studio tool which is part of SDK you already installed into ProgramFolder (x86)\Orion SDK\

Run the studio and enter the FDQN name of your Orion server (or IP Address), Administrator credentials and from drop-down select "Orion (v3)" which defines version 3 of SWIS.

pastedImage_16.png

If the connect is successful you'll see the SWIS database structure on the left hand side:

pastedImage_17.png

And again, this is not equal to your SQL server structure, this is the database structure Orion primarily use for viewing data and it's strongly recommended to use over direct SQL access because it provides data consistency, performance optimization and it will guarantee your reports to be working even if database structure is changed during product updates.

Prepare the SWQL query

Not the fun begins and we need to define SWQL query for our data set.

Here is the one I use to get the data for my SLA report:

select n.Caption, sub_query.sla_day, sub_query.sla_availability, n.CustomProperties.time_from, n.CustomProperties.time_to FROM

(

   SELECT avg(Availability) as sla_availability, datetrunc('day',datetime) as sla_day, r.NodeID

   FROM Orion.ResponseTime r

   where hour(datetime) >= r.Node.customproperties.time_from and hour(datetime)<= r.Node.customproperties.time_to

    group by datetrunc('day',datetime), r.NodeID

) as sub_query

inner join Orion.Nodes n ON n.NodeID=sub_query.NodeID

For those who are bit familiar with any SQL type of language it should look familiar. Here is what I've done

First I need to compute average data from Orion.ResponseTime table and I need to apply rush hour limitation defined by custom properties from above. This represents inner query. "Datetrunc" function is able to take a day, hour or month from entire date. I need a day so I can guarantee avaliability for each node is computed for only a given day and then for rush hours time interval. It's easy compare condition.

Second I need to return the results of average node availability for time-segment and apply a table formatting so I will see "Node Caption", day/date, availability and time_from and time_to defining SLA business rush hours.

And the result?

pastedImage_22.png

Great, so we can tune our query and add/remove columns or apply additional sorting.

Publish and view in Orion reporting.

Time to take our work and make it available for all Orion users. To do so, go to Orion web console -> reports -> manger reports -> create new report. And we will create new CustomTable based report.

Follow the steps below to accomplish your task where we enter our SWQL query, select the columns for report and apply sorting by date:

1)

pastedImage_24.png

2)

pastedImage_25.png

3)

pastedImage_26.png

4)

pastedImage_27.png

5)

pastedImage_28.png

6)

pastedImage_30.png

7)

pastedImage_32.png

😎 voila - here comes the final report

pastedImage_33.png

We're done. Till now our new report "Node SLA report - business hours" will be available in the list of reports in Orion and you can also schedule the report on daily/weekly basis as you need.

Please be aware our maintenance rolls-off the finest detailed data each month (30 days) and if you run the report after that period you won't be able to see full granular details beyond 30 days time border.

I hope you find this useful and it inspires you to experiment more with SWQL and SWQL studio in order to build the reports you're looking for. SWQL studio will also help you to browse our data structures so you can learn by walking and master your custom report skills in Orion.

25 Comments
Level 14

We are looking into beefing up our official documentation around SLA report customization. If you have used these instructions, we would like to talk to you (and of course there would be a reward of THWACK points). Please email me - jennifer.kuvlesky@solarwinds.com - if you are interested in providing feedback to our Technical Content team on how we can best help you improve your ability to create custom SLA reports.

This is massively, massively more complicated than it needs to be. I could make a document of how to do this in about....4 images?

You could just use Business hours reporting in the Web Based Report Writer  - note the post by HolyGuacamole ​about 1/3rd of the way down the page, and do this with no SWIS and no SQL. Plus the timestamp would be natively defined and wouldn't have a weird column space using a custom property.

Short version: define selection, define business hours, have web report done.

I'd be happy to discuss both documentation and report customization jkuvlesk​ . Those are definitely areas that I'm working with currently that I have a lot of feedback.

Level 16

The purpose of this blog post is different than using time_from and time_to in reporting. That will simply cut the source data within the interval you specify, however reporting date_from and date_to has no relation to nodes, applications or any other Orion entity for SLA you want. It's usual that no all nodes falls into the same SLA window but you want to have all nodes with their SLA in a single report and that's what is this "how to" post about.

Imaging you need to deal with Nodes in different time zones as the Thwack user was referring to, you can use the same approach and create custom property "time_zone" for Nodes and then apply a number representing node GMT timezone (for Austin it would be -6, for London nodes 0, etc.) and then you use that custom property in the query above where you simply add it as an offset to the availability datetime which converts the node to it's timezone and you'll get correct data for Node availability on the same report.

That's very useful to know actually, and makes sense. I stand corrected! Thank you.

Level 7

This is great, I need this for Group Availability though... Let's see if I can fit this into our setup. We don't want to see scheduled maintenance effect our availability reports and show downtime that is known, only the unknown do we care about.

Level 14

I built this in 15 minutes, including a custom hot rod logo for my boss's enjoyment.  Easy-peasy Mac & Cheesy!

Nice work--thanks!

(a fine toothed comb will reveal a few typos in your instructions--a word to the wise)

Level 14

Yes, my mom was reading thru this post and noticed the who're and her eyebrow raised.

Not sure if that's an actual word ...

Level 16

haha, thanks Orion, it's fixed by now.

The typos I referenced earlier are:

pastedImage_0.png

Level 10

Great post!

Is there a way to drop off weekend days?

I tried to use ((DATEPART(dw, datetime) + @@DATEFIRST) % 7) NOT IN (0, 1), but datefirst function is not available in SWQL.

Level 14

This is a great post and is going to help me work on SLA reports.  Is there any place either here on Thwack or the SolarWinds site that I could learn a bit more on the SWQL queries?  I designed one to show our network infrastructure and it grabs data from back 3 months.  So looking for ideally an area to reference what the different query items do.  Sorry I am not a SQL person at all - so like how I could modify the query to show only one week of data.   Thanks! 

Been there, built it, loved it!

Level 11

It would be great and also if we can consider unmanage and manage time frames ???

Level 14

Hi Martinan monster (love your handle), I think you will find this article useful, hot off the presses. https://support.solarwinds.com/Success_Center/Network_Performance_Monitor_(NPM)/Understanding_the_Cu... We are also planning a training early next year - February - sign up in the customer portal: Expert Series: Create Custom SLA reports with SWQL.

How do I get to that link?  I can click on it and it takes me to a login page that neither my SW customer account or my Thwack account do not work....

Level 14

Can you try again? You should not need credentials to view the article.

That worked thank you!

Level 19

You can get the day of the week from a date in SWQL using the "WEEKDAY" function. So to exclude weekends, use "WEEKDAY(datetime) NOT IN (0, 6)".

This function was missing from https://github.com/solarwinds/OrionSDK/wiki/SWQL-Functions, so I just added it.

Level 10

Thanks tdanner!

Level 10

If I use month in datetrunc function, that returns two separate dates with first dates of months.

pastedImage_0.png

This happens because availability calculation is spanned on two separated months, right?

And if data retention is with default settings, last month early days might be gone.

If I want to make monthly reports, report should be run first day of month in early hours to get most accurate results, right?

Or last day of month in late hours.

Hello everyone!  I have this SLA uptime report running for 5 servers.  The report I generate will list each server and its uptime for 30 days.   Is there any way to take that uptime and condense it to one number?  I am looking to report on the uptime for working hours but have it take the 30 days and list it as an average of the 30.  Thanks and if this does not make any sense let me know.  - Dave

Level 14

We now have a recorded training on this topic in the Customer Portal. https://customerportal.solarwinds.com/VirtualClassrooms/#0  Search for the class, Create Custom SLA Reports with SWQL.

About the Author
I joined SolarWinds PM team in September 2011 and I am currently part of product management team located in Czech Republic . Before that, I spent five years developing networking products and mobile applications.