All Apps and Add-ons

What are the steps to configure an incremental poll for the "Solarwinds Query" Input (Custom Query) in the SolarWinds Add-on for Splunk app ?

jeffrey_berry
Path Finder

What are the steps to configure an incremental poll for the "Solarwinds Query" Input (Custom Query) in the SolarWinds Add-on for Splunk app ?

Per the documentation, it sounds like only the "SolarWinds Alerts" Input allows an incremental poll while the "SolarWinds Node Inventory" and "SolarWinds Query" Inputs use a snapshot poll only. If an incremental poll is not possible for the "SolarWinds Query" Input, has anyone deployed a workaround to this apparent lack of feature? For example, has anyone used the "Splunk Add-on for Microsoft SQL Server" app to input data into Splunk from the Solarwinds DB?

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

You would not want to generally connect to the SQL DB behind the scenes, it's far more messy and convoluted and a lot of work to do anything useful with it.

But, you can essentially use a time as a rising column inside your query via the SDK/SWQL. There are issues with this, but it should work well enough.

Also note that I'm only like 75% complete in implementing this myself - I broke our SW a few times and the SW guy told me to lay off and let it rest for a bit to catch its breath. 🙂 Still, I see no reason it won't work - I do this with DB inputs all the time and it's the same sort of thing as that.

OK, enough notes and warnings - on to the workaround!

If you have the input running at a schedule of, say, every 5 minutes, you want each round of collection Splunk to go back 5 minutes, right? Find a timestamp column in your data, usually called DateTime, and we'll use that plus a bit of math to filter to only the last 5 minutes.

Since the dates in my install are all stored in UTC, we need to get the current UTC date, then subtract 5 minutes from it. So if it’s 8:41 AM right now, we want to get our current time in UTC which would be 2:41 (I'm Central Standard Time), subtract 5 minutes from it to get 2:36, then filter to where our datetime is newer than that. The starting point for this is the GetUTCDate() function, and using that plus the AddMinute() function with a negative number (to subtract) you'd end up with a query for your input that looks like the one below. You could use GetDate() if yours are not in UTC but are in local time, you'll have to figure that out yourself. See the references section under "SWQL Functions" for more information.

SELECT NodeID, DateTime, Archive, OtherFieldsHere...
FROM Orion.CPULoad CPU
WHERE CPU.DateTime > AddMinute(-5, GETUTCDATE() )
  AND CPU.Node.Caption = 'MyServerName'

This uses several tricks.

The first is the alias of Orion.CPULoad to CPU because I'm lazy and don't want to type it. I have also found it to be occasionally problematic if you try to use the full non-aliased specifier in front of things, like Orion.CPULoad.DateTime - that may have only happened early on before I understood how the SWQL worked as well, but since this is useful syntax anyway I'll just keep using it.

The second is you'll see I filter it to my server name with CPU.Node.Caption, which is using a linked table (see the "How to use SWQL" reference page in section "Navigation Properties" for for more information on that) to filter by the Node Caption. Essentially, SWQL will "automatically join" tables together when related and when there's a linking node name, like in this case Node. So the "field" Node in CPULoad essentially links to Orion.Nodes, so CPU.Node.Caption is actually the field Orion.Nodes.Caption.

To see more information, see the references below on the Orion SWQL Studio and the schema.

Other references:
Download the SWQL Studio to make investigating this slightly easier.
A link from SW on how to use the SolarWinds Query Language itself.
Reference data on the SWQL Schema.
SWQL Functions - The SWQL Functions Page.

I hope this helps, and happy Splunking!
-Rich

View solution in original post

0 Karma

jeffrey_berry
Path Finder

Thanks rich7177 for your response...

After some research, we used the "Splunk DB Connect" app (similar to the "Splunk Add-on for Microsoft SQL Server" app") to get data from our Solarwinds installation. "Splunk DB Connect" app offers significantly more features than the "SolarWinds Add-on for Splunk" app.

However as you alluded, it requires some level of knowledge of the Solarwinds DB schema to SQL query the Solarwinds DB effectively. The SWQL API can be easier at first look because data is organized into single "SWQL tables". MS SQL Server has tools like SQL Profiler to "sniff" the SQL queries that the Solarwinds application and/or SDK executes to gain an understanding of the Solarwinds DB schema. Also, the SWQL queries (aka Solarwinds API function calls) used by the "SolarWinds Add-on for Splunk "app are logged to the "_internal" index of Splunk in an http url encoded string which can be translated into SQL queries if you have enough knowledge of the Solarwinds DB schema and http url encoding. An example Splunk search query to help find the SWQL queries of the "SolarWinds Add-on for Splunk" app logged to the Splunk _internal index is below.

index="_internal" nodes source="*solarwinds*"

Our biggest issue for adding new capabilities related to Solarwinds at this time is our version of Solarwinds NPM is several years old. The "SolarWinds Add-on for Splunk" app is not compatible with older versions of Solarwinds NPM and community supported.

0 Karma

Richfez
SplunkTrust
SplunkTrust

Glad you have the skills to handle that - I had a similar thought process but IMO it came down to two issues: one is time - I think the fastest/easiest way to determine the direct SQL required was to learn enough from the SWQL to be able to use it, then profile some of the more useful looking SWQL queries to find out what they do on the back end, but that's ... some work. The other is time - or did I mention that already? 🙂

Perhaps this would be a great place to mention there is a somewhat untended Splunk Wiki - http://wiki.splunk.com/Main_Page
I made an attempt at reformatting and cleaning up a few of the existing pages a year or two ago, but didn't get far (again, time).

Perhaps a NEW topic like "Getting data from SolarWinds" won't suffer the same issues as I had trying to clean up old stuff. It should be faster/easier to drop a few new pages in and add a bit of content as I find it than it is to clean up old content that all has to be confirmed. So maybe we can press the wiki into service a a sort of clearinghouse of some basic examples as they're found?

Anyway, I'm glad you've found a method to do what you need!

Happy Splunking,
Rich

0 Karma

Richfez
SplunkTrust
SplunkTrust

You would not want to generally connect to the SQL DB behind the scenes, it's far more messy and convoluted and a lot of work to do anything useful with it.

But, you can essentially use a time as a rising column inside your query via the SDK/SWQL. There are issues with this, but it should work well enough.

Also note that I'm only like 75% complete in implementing this myself - I broke our SW a few times and the SW guy told me to lay off and let it rest for a bit to catch its breath. 🙂 Still, I see no reason it won't work - I do this with DB inputs all the time and it's the same sort of thing as that.

OK, enough notes and warnings - on to the workaround!

If you have the input running at a schedule of, say, every 5 minutes, you want each round of collection Splunk to go back 5 minutes, right? Find a timestamp column in your data, usually called DateTime, and we'll use that plus a bit of math to filter to only the last 5 minutes.

Since the dates in my install are all stored in UTC, we need to get the current UTC date, then subtract 5 minutes from it. So if it’s 8:41 AM right now, we want to get our current time in UTC which would be 2:41 (I'm Central Standard Time), subtract 5 minutes from it to get 2:36, then filter to where our datetime is newer than that. The starting point for this is the GetUTCDate() function, and using that plus the AddMinute() function with a negative number (to subtract) you'd end up with a query for your input that looks like the one below. You could use GetDate() if yours are not in UTC but are in local time, you'll have to figure that out yourself. See the references section under "SWQL Functions" for more information.

SELECT NodeID, DateTime, Archive, OtherFieldsHere...
FROM Orion.CPULoad CPU
WHERE CPU.DateTime > AddMinute(-5, GETUTCDATE() )
  AND CPU.Node.Caption = 'MyServerName'

This uses several tricks.

The first is the alias of Orion.CPULoad to CPU because I'm lazy and don't want to type it. I have also found it to be occasionally problematic if you try to use the full non-aliased specifier in front of things, like Orion.CPULoad.DateTime - that may have only happened early on before I understood how the SWQL worked as well, but since this is useful syntax anyway I'll just keep using it.

The second is you'll see I filter it to my server name with CPU.Node.Caption, which is using a linked table (see the "How to use SWQL" reference page in section "Navigation Properties" for for more information on that) to filter by the Node Caption. Essentially, SWQL will "automatically join" tables together when related and when there's a linking node name, like in this case Node. So the "field" Node in CPULoad essentially links to Orion.Nodes, so CPU.Node.Caption is actually the field Orion.Nodes.Caption.

To see more information, see the references below on the Orion SWQL Studio and the schema.

Other references:
Download the SWQL Studio to make investigating this slightly easier.
A link from SW on how to use the SolarWinds Query Language itself.
Reference data on the SWQL Schema.
SWQL Functions - The SWQL Functions Page.

I hope this helps, and happy Splunking!
-Rich

View solution in original post

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!