animelov

Applying SWQL Part 2

Posted by animelov Employee Nov 13, 2018

Hi, all! Welcome back to the continuation of our Primer posts on SWQL and the Orion® SDK. In the last post, we showed how to create dashboards using SWQL queries. Now we’re going to take it one step further with some other uses for SWQL:

 

Dashboards:

As with the reports, you can also add a custom SolarWinds Query Language (SWQL) query to a dashboard. If you aren’t familiar with customizing dashboards and widgets, check out these videos first:
Creating a New View
Adding and Customizing Resources

 

To get started, make sure you’re logged in as an admin to SolarWinds, or a user that has rights to make updates/changes to views. Once that’s confirmed, go to the page you wish to update, and go to the left-hand drawer and select “Customize Page.”

Search for “Custom Table” and drag and drop the widget onto your dashboard. There’s also “Custom Query,” and we’ll explore the advantages further down:

Select “Done Adding,” then “Done Editing” when complete. With your newly created widget, go ahead and select either “Edit” in the upper-right, or “Configure this resource” in the middle:

This should look familiar to the report writer’s interface at this point. Give this table a title, then click “Select Datasource.”

Change the Selection Method to “Advanced Database Query (SQL, SWQL)” and make sure the radio button is set to “SWQL.” Then copy/paste your query and preview results to make sure everything looks okay:

Select “Update Datasource” when complete. Just like the report writer above, you can select and format your columns. Once you’re finished, click submit, and you now have a custom table on your dashboard!

 

Note: The Host Name column being blank isn’t an error, these machines are not associated to a host.  We’ll explore formatting in a later post to show these as “N/A” instead.

 

Now, let’s try the custom query instead. With the “Custom Query” widget, you don’t have as many options in formatting, but it gives you two distinct advantages: the ability to paginate, and the ability to add searches. Pagination will be very important for larger lists, not only for cleanliness, but also for load times on the page you’re viewing, by restricting to X number of results at a time.

 

Again, go to the left-hand drawer and select “Customize Page,” then “Add Widget.” This time, search for “Custom Query” and drag/drop this widget to your dashboard:

Now, select “Edit” in the upper-right corner of the widget:

Notice here, you only get a box where “Select Datasource” would normally be. Go ahead and copy/paste your query in here, but since you don’t get the option of selecting the order of the columns, make sure your columns in the select statement are in the order you want them in. For example, with our query:
select OAA.Displayname, OAA.Status, OAA.Node.Caption, OAA.Node.VirtualMachine.Host.HostName from Orion.APM.Application OAA

Displayname” will be the first column, “Status” will be the second column, and so forth. So now that we have this:

That will result in a widget that looks like this:

Notice the “Page 1 of 2” at the bottom? This will help reduce clutter on your dashboards by keeping the list neat and tidy, and at the same time help with page loads, since we’re restricting to only five results. Another cool feature is the “Search” function. Edit the widget again, and this time check the “Enable Search” box:

Now you have another box to insert your query, and a note about adding a where clause for the search string. When we’re finished, we’ll have a search box on the widget page, and whatever you put in that box will go into the ${SEARCH_STRING} variable. This will change our query to add the where clause. In this case, we’re going to search on the Application name, which is our first column:

 

select OAA.Displayname, OAA.Status, OAA.Node.Caption, OAA.Node.VirtualMachine.Host.HostName from Orion.APM.Application OAA WHERE OAA.DisplayName like ‘%${SEARCH_STRING}%’

 

The keen-eyed individuals will notice we added just a little bit more here. In SWQL, if you want to do a wildcard match instead of an exact match, you use the word “like” instead of “=”. Then, you use the percent character (%) to denote a wildcard, not an asterisk (*). Finally, in SWQL you always use single quotes for strings, never double quotes. Let’s put that in our search box:

And now we have a search box!

To test, let’s search on IIS and see what we get:

There we go! Remember, this is just an application example, you can use this for anything else that you’re collecting in the product. For more examples, check out my other post for searching on a Port Description in User Device Tracker (UDT): https://thwack.solarwinds.com/docs/DOC-192885

 

 

That’s it for now! Stay tuned for future posts on formatting SWQL queries in these reports!

SQL Server upgrades are a pain, I know.

 

And boring, too. It’s not very exciting to watch a progress bar.

 

Many people put off upgrading SQL Server. They wait for a business reason or an important security patch. Or, as was the case historically, they wait for the first service pack. After all, if it ain’t broke, don’t touch it.

 

I’m here today to tell you those days are over.

 

No longer can you sit back and allow systems and applications to lag behind with regards to patches and upgrades. You must stay current. Allowing applications to be more than one major version behind puts you, and your systems, at greater risk for security threats than ever before.

 

Microsoft has made it easier to upgrade and patch SQL Server. They’ve removed service packs, opting instead for cumulative updates. By shifting to a model that is similar to continuous deployment, Microsoft is able to deliver features, performance improvements, and security enhancements at a faster rate than ever before.

 

So, if you are waiting for SQL Server 2017 SP1, you’ll be waiting forever.

 

Don’t wait. Get started on upgrading SQL Server to the latest version today.

 

Let me help you understand just a few of the reasons why upgrading SQL Server is right for you.

 

Reasons for Upgrading SQL Server

As I mentioned before, it’s just common sense to stay current with the latest version of SQL Server. Microsoft has built tools like the Database Migration Assistant to help make upgrades easy. Applying cumulative updates has also been simplified. And because Microsoft hosts millions of database workloads inside of Azure SQL Database, you can be assured that these updates have been tested thoroughly.

 

Here’s a handful of the features available, out of the box, when you upgrade to the latest version of SQL Server.

 

Automatic database tuning – The ability for the database engine to identify and fix performance problems.

 

Adaptive query processing – While processing the execution plan, SQL Server will adapt query plans as necessary, essentially tuning itself instead of reusing the same plan.

 

Data security and privacy featuresAlways Encrypted, Dynamic Data Masking, Row Level Security, Data Discovery and Classification, and Vulnerability Assessment are all new, and all awesome.

 

Those are just a handful of the improvements. You will also find things like faster DBCC CHECKDB, improved backup security, and a new cardinality estimator. All those are great features worth your time for upgrading.

 

But there’s one more thing: the Orion® Platform.

 

See, we’ve been busy refactoring the Orion Platform to take advantage of newer SQL Server features.

 

Reasons for Upgrading Your Orion Installation

When I’m at an event performing demos, I am surprised how many customers haven’t upgraded to the latest version of the Orion Platform. Of course, I understand the many reasons why upgrades are put on the back burner.

 

I’m here today to help you understand that there’s more to the latest Orion version than a few fancy screens.

 

By using columnstore indexes, we have reduced the size of the Orion database (up to 33% less space), the amount of time it takes to perform maintenance (up to 6x faster on average), and the amount of time to retrieve data (up to 10x faster). That’s a lot of performance gains.

 

Table partitioning allows Log Manager for Orion to scale, accommodating multiple log sources, and the ability to quickly display all logs in time sequential order. As anyone that has had to analyze logs will tell you, it’s important to be able to quickly see all events in the exact order they occurred.

 

Also, in-memory OLTP helps products that leverage the Orion Platform achieve a high rate of concurrency, accelerating performance and scalability.

 

Those features sound great, but don’t just take my word for it. You should read about the SQL Server features being used by NetFlow Traffic Analyzer (NTA) over at this FAQ page.

 

Now, at the bottom of that page, I want to call out something else that you will find interesting…

 

“You can install your NTA Flow Storage database and your Orion database in the same instance of MS SQL, provided that instance is an MS SQL 2016 SP1 or later version.”

 

That’s right, upgrading to the latest version of NTA allows you to consolidate your SolarWinds footprint. For customers paying by the core for SQL Server licensing, this alone should motivate you to upgrade.

 

I’ll make it easy for you: here’s a link to help you get started. Also, here’s the official upgrade guide located on our Customer Success Center.

 

I’ve also written some other in-depth posts about tips and tricks on upgrading SQL Server. Have a look—I believe you’ll find the information useful.

 

Summary

At the end of the day, we want the same thing that any company would want: happy customers.

 

By upgrading to the latest version of SQL Server, and then the Orion Platform, our customers can see benefits immediately. Not just in performance, but in your wallet.

 

Continuous improvement is the world in which we live now. Stop thinking of upgrades as a chore or a task to get past. Upgrade because you want to, not because you have to.

 

The SolarWinds trademarks, service marks, and logos are the exclusive property of SolarWinds Worldwide, LLC or its affiliates. All other trademarks are the property of their respective owners.

If you have installed or upgraded any Orion® Platform product module over the course of the last six months and were running Orion product modules on either Windows Server 2012, Windows Server 2012 R2, or SQL Server 2012, you probably noticed an ominous warning message notifying you that these operating system and SQL database versions are deprecated and will no longer be supported in a forthcoming release.

 

Windows Server 2012 / R2 Deprecation NoticeMicrosoft SQL Server 2012 Deprecation Notice

 

 

If you didn’t encounter this message during your latest upgrade or install, not to worry. The above message only appears if Orion product modules are being installed or upgraded on an operating system or SQL database version that has been deprecated. If you're running several versions behind but have been keeping tabs on the release notes, eyeing all the wonderful features that await you when you do your next upgrade, you will find a similar deprecation verbiage there for every Orion module letting you know that you should upgrade from Windows Server 2012, Server 2012 R2, and SQL 2012 at your earliest convenience to stay current with later releases.

 

 

So what exactly is the purpose of these deprecation notices and why should I care?

 

Deprecation notifications such as these serve as sign postings to our customers of important impending changes in the matrix of operating systems and SQL database versions that will no longer be supported in future releases. These types of advanced notices were introduced at the request of customers like you. Their intention is to allow you ample opportunity to upgrade your environment prior to the release of newer Orion product module versions where these operating systems and database versions may no longer supported.

 

 

Life before Deprecation Notices

 

Prior to the inclusion of these deprecation notices, the only real way of knowing if an operating system or database version was no longer supported in the latest release of the Orion Platform was to download and attempt to install it. This was obviously much too late in the process, as by this point you likely only received approval from the change advisory board to upgrade your Orion install, and your window for downtime was narrow enough only to allow for the upgrade of your Orion product modules and not the operating system or database server that your Orion Platform resided upon. As you could imagine, this was a frustrating or even downright infuriating time to find out your upgrade was blocked. To prevent these types of mishaps from occurring, SolarWinds provides in-product deprecation notices one version in advance, warning customers that future releases are unlikely to support these older operating systems or SQL database versions.

 

 

My OS or SQL database version has been deprecated. How am I affected?

 

In short, you're probably not. These deprecation notices apply only to the absolute latest releases and are not applicable to previous versions of the product. There has always been zero requirement that customers upgrade to the latest version to continue receiving support. While we welcome and encourage all our customers to take full advantage of the latest enhancements and improvements included in newer versions of the product, this is not always possible or practical in every customer environment. Some organizations even have firm constraints that require them to stay at least one version behind the latest at all times.

 

For those reasons and more, we continue to fully support several previous released versions of Orion product modules at any given time. Suffice to say, if you're currently running NPM 12.3 or any other Orion Platform 2018.2 module release on Windows Server 2012, 2012 R2, or SQL 2012, there is no immediate impending requirement to upgrade. SolarWinds end-of-life policy helps ensure that these versions will remain fully supported, even when installed on Server 2012, 2012 R2, or SQL 2012.

 

 

Why are you deprecating my otherwise perfectly fine operating system or SQL database version?

 

Going forward, the Orion Platform and its related modules will begin to leverage new technologies only available in newer versions of SQL and Windows. New capabilities such as In-Memory OLTP, columnstore indexes, as well as partitioned tables and indexes aim to improve various aspects of performance and scalability for the entire Orion Platform, as well as the modules installed atop it. This will allow for accelerated website performance, shorter nightly database maintenance routines, reduced database size, and faster report generation, to name only a few areas of noticeable improvement.

 

Windows Server 2016 and 2019, as well as the version of IIS included with them, provide a host of important new security improvements that are critical to organizations of all sizes. These include things like supporting newer, stronger encryption ciphers, HTTP Strict Transport Security (HSTS) enabled websites, secure cookies, and more. While patches for specific critical security vulnerabilities will still be made available for Windows Server 2012 and 2012 R2, vital new security enhancements, bug fixes, and other notable improvements will only be available to later versions of the Windows operating system still under mainstream support.

 

 

How can I better plan for possible future OS and SQL deprecations?

 

While SolarWinds does everything reasonably possible to help ensure customers stay well informed of impending deprecations, some have asked for a longer-term outlook so they can plan their upgrade and server migration schedules accordingly. First, when selecting which operating system or database version to install Orion product modules on, we always recommend using the latest possible version of both. This decreases the likelihood of that operating system or database version being deprecated anytime in the foreseeable future, while also limiting the number of times you need to migrate your Orion installation to a newer server throughout its lifetime. To stay proactively ahead of any impending deprecation notices, however, you need only look to Microsoft's published product lifecycle for Windows and SQL Server.

 

Put simply, the Orion Platform will support Windows operating system and SQL database versions covered under Microsoft's Mainstream support that are available at the time of that versions GA release date.

 

 

I still have Windows and SQL 2012 in my environment. Can I continue monitoring those systems with Orion?

 

Absolutely! Monitoring Windows Server 2012 and SQL 2012 systems with the Orion Platform and its related modules remains fully supported, even in the latest releases. This support also extends to those systems monitored using the Orion Agent.

 

 

What Windows and SQL server versions exactly should I expect will be supported in the release following Orion Platform 2018.2?

 

The following table outlines those versions of SQL and Windows Server that will be supported in the Orion Platform release following version 2018.2:

 

Supported Operating System VersionsSupported Microsoft SQL Server Versions
Windows Server 2016SQL 2014
Windows Server 2019SQL 2016
SQL 2017
Amazon RDS

 

 

I'm currently on Windows or SQL Server 2012. How do I upgrade?

 

In recent years, Microsoft has made the in-place upgrade process easier and more reliable than ever. In-place upgrades are likely also the fastest method for getting your Orion server up to the latest operating system or SQL database version. If an in-place upgrade isn't for you, SolarWinds provides a wealth of documentation on migrating your Orion Platform to a new server.

 

 

Also in the Success Center, you will find documentation on migrating your Orion database to a new SQL Server.

 

 

 

I need assistance with my next Orion upgrade, what options do you provide?

 

If you need a refresher course on the upgrade process, or a confidence boost that you're on the right track, you will find on-demand training videos and instructor-led virtual online classes you can attend for free through the Customer Portal. As always, if at any time you encounter an issue during your upgrade, don't hesitate to contact SolarWinds support for assistance. We are here 24 hours a day, seven days a week, 365 days a year to help ensure you are successful using SolarWinds products.

 

The SolarWinds trademarks, service marks, and logos are the exclusive property of SolarWinds Worldwide, LLC or its affiliates.  All other trademarks are the property of their respective owners.

Filter Blog

By date: By tag:

SolarWinds uses cookies on its websites to make your online experience easier and better. By using our website, you consent to our use of cookies. For more information on cookies, see our cookie policy.