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

Which table(s) in the Solarwinds database contains Component Status history?

Management wants me to extract all the component statuses (mostly Up, Down, Unknown, or Critical) for every component monitor for every node for as far back as we can get data. (They want to produce some reports in PowerBI).

Is [dbo].[APM_ComponentStatus_CS] the right view for this information? It appears to have each component, and "Availability" id, Timestamp, PercentAvailability, and Weight.

It appears that this view combines the _detail, _hourly, and _daily data into one, correct?

And Weight corresponds to how many polls in the given time period have the corresponding Availability status, right?

For example, if my row has this data:

ComponentID 393429
Availability 1<br />Timestamp 2023-12-18 00:00:00.0000000<br />ID 16455587278 <br />PercentAvailability 100<br />Weight 288

Then I think the "weight" is the total number of polls that occurred on December 18th, 2023, that had an "Availability" of 1, right?

A few more questions:

* Is there any other table that would allow me to go back farther than 2023-12-09? (I know there are some retention settings somewhere in Solarwinds, but those seem to indicate 365 days (see picture below)

* Where is a table that gives me each status for the "Availability" code?

* Is there a better way to go about fulfilling this request?

Thank you~!

Find more posts tagged with

Accepted answers

All comments

KMSigma.SWI

This is why we don't like people accessing this information directly from the database. As you've figured out, historical data is typically stored in three tables: Detail, Hourly, and Daily.

We recommend people query the API because when you ask for:

SELECT TOP 1000 [ComponentStatus].ID
     , [ComponentStatus].ApplicationID
     , [ComponentStatus].ComponentID
     , [ComponentStatus].Availability
     , [ComponentStatus].PercentAvailability
     , [ComponentStatus].TimeStamp
     , [ComponentStatus].RecordCount
     , [ComponentStatus].Archive
FROM Orion.APM.ComponentStatus AS [ComponentStatus]

The platform already knows all of the necessary tables to query.

ctech291

@KMSigma.SWI - thank you for the reply.

Is there a way to run a SWQL query via JDBC or ODBC connection? The tools I have at my disposal currently limit me from grabbing hundreds of thousands of rows via REST API.

Thank you~

PS:

For those that are able to use the API, I've found this to be super helpful:

1. Download/install SWQL Studio ( Intro to SWQL Studio , https://github.com/solarwinds/OrionSDK )

2. You can use SWQL studio to build out your query, including the linked table notation. For example, I was able to query the component status, but also bring in the Application name and node name without having to do any joins:

SELECT TOP 1000 ID
, ApplicationID
, ComponentID
, Availability
, PercentAvailability
, TimeStamp
, RecordCount
, Archive
,cs.Component.Name as ComponentNM
, cs.Component.Application.DisplayName as ApplicationNM
, cs.Component.Application.Node.DisplayName as NodeNM
FROM Orion.APM.ComponentStatus cs

Within SWQL, you can then choose Edit > Copy As > ... to get the text that you can use in an API call to get a JSON response.

KMSigma.SWI

I honestly do not know much about JDBC (nothing at all) or how ODBC could be convinced to make an API call.

If you are required to use specific "database" connectors, then I can't help too much. Even though I know the database, tables, and views, I don't tell people how to query them for one specific reason: the database may change between versions, whereas the SWQL calls rarely do.

Yes, sometimes the API entities and properties change, but when they do they are documented multiple versions in advance.

FullName	IsObsolete	ObsolescenceReason
Orion.APM.DynamicEvidence	True	Orion.APM.DynamicEvidence will be removed in a future version of the product. Please use Orion.APM.DynamicEvidenceChart

Regarding the API, we've started to collect a bunch of information in the [mention:611dcbeac4a744629d169bd937026c93:393e7426ce8b492199490c0b60cb1f1e] as a repository of knowledge.

ctech291

@KMSigma.SWI - Thank you again for the reply.

I think I kind of found an answer: it appears that there is a Python Client that we can use to run SWQL queries: https://github.com/solarwinds/orionsdk-python

We could also use powershell via the OrionSDK ( https://github.com/solarwinds/OrionSDK ). But I haven't found an ODBC or JDBC option, yet.

I asked about a JDBC/ODBC connection because I've used a solution in the past that had their own custom SQL language (similar to SWQL) and we could connect to the solution's server via their custom client (similar to SWQL Studio) or JDBC or ODBC and whichever connection we used, we could run the custom SQL language (so if you didn't want to use their tool, you could use any other tool that allowed for a JDBC or ODBC connection).

I was hoping that solarwinds had something similar. Yes, SWQL Studio is awesome, but if I were somehow able to connect to my solarwinds server via JDBC or ODBC and then run SWQL statements, that would provide me what I need and allow me to avoid querying the underlying tables directly).

Thank you~!

mesverrum

SAM is extremely chatty in the DB, so default retention periods are shorter for it, 90 days is the OOTB max for daily component stats.

Adjust data retention settings in SAM (solarwinds.com)

I wouldn't recommend someone just bump it up to 365 without considering that it can make SAM pages take a LOT longer to load if you have a good-sized environment.

mesverrum

I'll also throw it in there that most the PowerBI teams I have seen just ingest csv versions of reports form Orion instead of hitting the DB directly, its a bit more manageable because you just send them exactly the data they need and nothing else, and insulates your prod system from them trying to run a heavy report that slows down the whole production UI for your users.