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
Store

Report for availability/uptime for 8 specific routers.

I have tried a couple different methods, but I'm not to familiar with swql. I was able to pull certain info, but certain other queries I run in the db don't necessarily extend to the report writer. It will take me a while to get a hang of it. I want to report on the availability for last month, from business hours 7am to 7pm.

I tried the following:

SELECT [data].[DisplayName] AS [DisplayName],[data].[InstanceSiteId] AS [InstanceSiteId]
FROM [dbo].[nodes] AS data
WHERE
((([data].[Caption]) = ('router1')) OR (([data].[Caption]) = ('router2')) OR (([data].[Caption]) = ('Router3')))

That seems to atleast select the devices I wanted, the question is the how to report for the specific time intervals. I tried te dynamic query builder but I can't really apply that to the devices in a custom table individually. Any help or direction or kb articles would be appreciated thanks!

Find more posts tagged with

Accepted answers

All comments

mesverrum

You can do business hours in the GUI, https://support.solarwinds.com/SuccessCenter/s/

MEKVII

Thank you, I've realized I just need to lean more sql to get exactly what I want. So far I have it set up as seen in the image. The last thing I haven't been able to include is limiting the report to the business week, so mon-fri. Maybe I can create a custom property, working on that now. Noticed some of them are not report, or showing up without a number.

jm_sysadmin

OK, I think this is possible, tell me how you want to see it, columns are you hoping for? What grouping?

FYI I am starting with https://thwack.solarwinds.com/t5/Discussions/Monthly-Quarterly-Business-hour-SLA-Report/m-p/220182

We can alter it based on your needs. Adding in the list of 8 routers and hard coding the buisness hours from 8 am to 5 pm looks like:

SELECT N.Caption, DATETRUNC('day', TOLOCAL(N.ResponseTimeHistory.DateTime)) AS Day,SUM(N.ResponseTimeHistory.Availability*N.ResponseTimeHistory.Weight)/SUM(N.ResponseTimeHistory.Weight) AS SLA_AvailabilityFROM Orion.Nodes NWHERE HOUR(TOLOCAL(N.ResponseTimeHistory.DateTime)) >= 8AND HOUR(TOLOCAL(N.ResponseTimeHistory.DateTime)) < 17And (N.Caption like 'Router1' or N.Caption like 'Router2' or N.Caption like 'Router3' N.Caption like 'Router4' or N.Caption like 'Router5' N.Caption like 'Router6' N.Caption like 'Router7' N.Caption like 'Router8')GROUP BY N.NodeID, N.Caption, DATETRUNC('day', TOLOCAL(N.ResponseTimeHistory.DateTime))

You may want to build assign custom properties to find what needs to be in the report so that you don't have to do much work updating the report.

MEKVII

Thank you, I appreciate your help with this, it did produce some output however I will have to fine tune it to show a bit more detail. This is a good starting point though and pretty much what I'm looking for. I'd like the report to show availability and response time for business hours 7-7 pm and Mon-Friday only. I had some luck with the report writer but not as much detail as if it's done with the custom sql. Thank you again for your help! @jm_sysadmin

This is how the output looks currently with the sql code you provided.

Ideally it would look something like this:

jm_sysadmin

I like what I see in the screenshots, @ me if you need anything else.

MEKVII

Hey @jm_sysadmin , hope your doing well during this covid situation. Just wanted to follow up, I think it's worth digging since alot of users have this same question I'm sure. So I wanted to add these components to the sql query you provided, from the image below. As it is now I have the report with those parameters using the report builder, but I don't really think the time from /time to portion is actually working for those time periods. Reason being in the edit report summary it shows this (Image below). I reached out to sw support and they advised they can only do best guess trouble shooting for this, basically said the only way to get it set up is a custom sql query. Which if that is the case, is there a way to see what the query is for the parameters I set up in the layout builder? Thanks again, cheers.