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
((([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!
OK, I think this is possible, tell me how you want to see it, columns are you hoping for? What grouping?
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_Availability FROM Orion.Nodes N WHERE HOUR(TOLOCAL(N.ResponseTimeHistory.DateTime)) >= 8 AND HOUR(TOLOCAL(N.ResponseTimeHistory.DateTime)) < 17 And (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.
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:
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.
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.