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
Store

Intensive Query to query NCM.Configarchive which contains an IP Address

Hi Tech Guru's out there,

I am trying to query NCM.Configarchive to find an IP address in the config file but it is not working when i include more conditions:

SELECT TOP 5 ConfigArchive.NodeProperties.Nodes.Caption, ConfigID, NodeID, Config
FROM NCM.ConfigArchive
where (`ConfigArchive.NodeProperties.Nodes.Caption` LIKE 'FW1%' OR `ConfigArchive.NodeProperties.Nodes.Caption` LIKE 'LB1%' ) AND DownloadTime > ADDDAY(-1, GETDATE() AND config like '%10.X.X.X %')-----------------is not working,

but when i simply try,

SELECT TOP 5 ConfigArchive.NodeProperties.Nodes.Caption, ConfigID, NodeID, Config
FROM NCM.ConfigArchive ------------------------>works fine

Is there any better way to search config archive with a better Query?

Find more posts tagged with

Accepted answers

All comments

mrxinu

When you say it's not working, what do you mean? It's timing out? It's erroring?

mesverrum

To be fair you threw a wildcard search into the middle of all the configs. There is not any kind of indexing inside that column and it is often thousands of characters long. That's really the kind of thing that is really painful to do in sql. You'll probably have better luck bypassing the api and directly doing a search like this in the database with something like SSMS or SSRS so that you aren't subject to the timeouts that SWIS has. Probably going to take a long time to do that search if you have more than a small number of devices in NCM.

antonis.athanasiou

You may also have better luck if that IP address is in any way inventoried in NCM rather searching that (scary) column with hundreds of characters long..!

If there's a command to strip that running configuration into a a few lines, will also be a better way of quering into the database

mrxinu

Another thought is parsing the files that get stored on the server each time a backup is taken.