My Patience is running low. I'm exhausted and shouldn't have to pull my sql admin for every report I need. Days and hours trying to write what are really simple reports shouldn't be like pulling teeth.
Pain points that even our sql admin couldn't get past are:
1. Timestamp column. We couldn't find one.
2. Availability percentage. The table we found listed either 100% or 0% not an actual accurate percentage.
3. Connection between tables. When you need to grab data from different tables you need to find a common point like interface id. Sometimes the common point is not on all tables.
We found a query in the old report writer that shows the availability using sum functions among other things. But my sql admin couldn't find a way to narrow the list down to specific interfaces during a specific time. After 2 days we gave up because we where going no where. A call to solarwinds resulted in them saying to open a feature request which I have done. A thwack search yield another query that was designed by another user and this one we got closer, but could not get useful data approved by management as they indicated the information wasn't useful to them.
What we where looking for is simple. One column for interface name (MPLS interfaces), Filtered for the last 7 days, another column with time stamp and availability. We are trying to figure out which MPLS interfaces need maintenance and may need upgrade and thus far network team has had to rely on other tools as solarwinds has not met their needs. Every time they ask me for a report they wait 1 week while I try to figure out how to get it completed involve the write people only to get frustrated when I have to turn around and say that report is not possible. It angers me and makes me want to tell my upper management to ditch this software and go with a different solution.
I am looking for help as I'm pretty sure we aren't the only company looking for interface reports. And we will be thankful to anyone who can either share a report they have or a query that we can run for our own reporting. And if you can recommend third party reporting that can better help us put reports together that would be golden for us. We've been really struggling with our teams losing trust in solarwinds when we have to turn around and tell them reports they want can't be completed.
Great information indeed. I myself have been using web reporting. it's just that 90% of the time I can't get the data 120% accurate to what I wanted exactly. It's always close enough. Then I would have to take that data and format it in excel or whatever the case was at the time. This got rather annoying. So I would jump here on thwack. Spend at least a good 2 hours or so scrolling through entries and looking at what other people have attempted. Some times I find answers and other times I don't.
I try support. But they are useless for customization which I don't blame them. They are there for break fix. And google search is just like thwack. Sometimes works other times it doesn't.
What I was looking for is for example. 10/15/2017 MPLS 100MB 10am 100%, 11am 100%, 12pm 89%, 1pm 50%, 2pm 100% and so on. Or if it's based on 0 100 which is fine also. How long was it at 0 and how long was it up, with a daily average of availability. And for the life of me I couldn't get this done even with custom sql queries.
I personally have the old report writer. But I figured what the hack. I got nothing to lose peeking in there. Found a query with a sum function that hit the average spot on filtered by the time frame exactly like we needed. But since it's sql I couldn't tell it to look for 7 days and I couldn't specify specific interfaces.
Marc is the man! He's been able to help through a few tough spots and that's great. I'll give you all an update after trying the above. And thank you all so much for the help. This is amazing , and appreciated.
It helps to have some familiarity with the reporting interface so you know where to find what you need but Solarwinds has a ton of info and there's not really any substitute for seat time in terms of learning where they stash everything. Could be a bit more intuitive but ultimately it isn't terrible to learn if you are in there.
If you are looking at old reportwriter stuff you are already heading in the wrong direction. Report Writer has not technically been deprecated but it should not be used unless you are intending to learn SQL yourself. It has no features that you can't do via the web UI except the "Show SQL" button.
Create a new report in the Web UI, add in a Table, where it asks you to pick your data source is where you apply filters, basically your WHERE conditions if you are working with a DBA. I'm going to use the Comments custom property but you can use whatever custom property or naming convention you need to specify the interfaces you need to look at.
Next pick your columns, you already stated you need a name, timestamp, percent of availability.
For the name there's a lot of choices you could pick from here but I find that Full Name is usually the easiest way to do this.
Next you want to get your availability stats and their timestamps, if you choose the one from the first screen you are on you would only be showing the current availability of any interface. You want historical values so you need to click into the Interface Availability History on the left.
So at this point is where it appears you had a misunderstanding about the data you are looking at. Availability will always be a 100 or a 0 because at any given poll the interface was always either up or down. The only case where you would see anything other than these is when you are looking far enough back in the history where you start seeing the hourly and then daily averaged values., on a default setup this would be after 7 days then 30. To change from the 0/100 to an average across the whole report period you need to just tell the report to average those values across the day.
So you can close the column chooser and click on the tab called availability and go down to the row called Data aggregation and set it to average.
This will give us the average across the entire time period, but if you want to get this split up into daily averages you just need to the time based settings and tell it to have a sample interval of Day
While I'm in here I also want to make it so you can click the interface name to get a link so go to Full Name and add the Details link option and enable the tooltips.
At this point we want to tell the report to group things based on the interface, then sort them by the timestamps
Submit the table section, give the report a name and define how far back the report will look.
This is what I get
Once you know where to look this kind of thing takes about 5 min to spin up. If I wanted I could filter it to specific business hours using the info from this KB Use Report Manager to configure business hours in web-based reports - SolarWinds Worldwide, LLC. Hel...
Hope that helps ease your frustration.
Loop1 Systems: SolarWinds Training and Professional Services
Excellent post mesverrum. I myself resisted web reports for quite a while and was determined that Report Writer was amazing and that web reports were not. However, after taking the time to learn web reports in Orion I wouldn't want to go back to the old Report Writer ever. There is so much you can do in web reports it is quite astonishing.
Yes, usability could definitely be increased as the learning curve is a bit steep at first and, even once you've learned it, there are often times where I have to resort to a custom SWQL table to get to the data I want. We'd also like the ability to setup easy to configure report templates that users could then "tweak" to their liking to create their own custom reports without having to get into all the minutia of setting up a custom table or chart in the edit report wizard.
For admins that are still determined to stick with old Report Writer or admins that are new: Trust me. Keep working with the web reports. Just keep playing with it and searching google/thwack for solutions to your problem and I guarantee you once you understand it you'll be able to do so much in the web report console and you will be able to throw together reports and even cooler views (because a web report really is just building a view in a different way) that your peers and managers will love in no time.
"For admins that are still determined to stick with old Report Writer or admins that are new: Trust me. Keep working with the web reports. Just keep playing with it and searching google/thwack for solutions to your problem and I guarantee you once you understand it you'll be able to do so much in the web report console and you will be able to throw together reports and even cooler views (because a web report really is just building a view in a different way) that your peers and managers will love in no time."
I honestly don't think I could use anything but web reports at this point; we've only had sw a short time but for the last 4 days straight I've just played with web reports and alert manager. In the time that I’ve been messing with it I’ve not only increased my knowledge of SQL/SWQL statements and correct functionality of alert manager conditions, I’ve actually become decent at whipping up quick reports that look quite impressive despite the short time it takes to create them.
Let me preface this by saying I'm relatively new to sw and basically know nothing. However sometimes new eyes can help, in reportwriter I find I can write a report on static objects such as interfaces, group them by node for datasource 1, select availability as the metric and % as the outcome over the last 7 days. I then select time period (in my case I chose every 15 minutes) then show all records and submit. This brings me to a chart (if you're looking for columns this might not be what you're looking for but there are other platforms available in reportwriter) which has my selected interfaces, the availability in % with a timestamp every 3 hours (which i'm sure you can probably narrow down). This was a relatively simple creation so it may not be at all what you're looking for. Hope it helps a bit. You're also definitely not the only company looking for interface reports.
Cool, let me know if it helps you out. Also depending on your SLA, availability over 7 days might not be the best metric to use to warrant maintenance as typically you'd be measuring the actual path availability the circuit is currently routing over (with MPLS) which may be inaccurate over 7 days? You might consider using a 30 day trend and unless you're trying to find out at what specific times of day a circuit is degraded or out, you might be able to get away with something even more simple depending on what kind of maintenance youre performing. For instance, if you go into reportwriter and use the report: interface downtime summarized - 7 days, it will tell you simply all the interfaces on your nodes and the total time in duration of minutes that each has been either up down or unavailable, which might even be more detailed than what youre currently trying to do.
It ends up looking something like this:
In this case Tunnel 1 was unreachable for 3 minutes, down for 3, and up for 170 - obviously over 30 days and across your enclave you might see all kinds of interfaces that are just completely out of whack or many that just say theyve been up for however many thousands of minutes.
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.