Summary
Question
(NA) Full Text Searching troubleshooting tips.
Answer
Overview – full-text search
Why do we need this?
NA stores all the device configurations and its history in a table named RN_DEVICE_DATA.
This store can grow very large depending on the number of managed devices and retention time set for configurations.
NA provides searches in reports, dynamic groups, and policies based on device configurations.
Search queries using SQL-LIKE will result in full table scans and the resulting performance of these searches can make the search unusable.
When this new feature is implemented, the following new operators, "contains(full-text)" and "doesn't contain(full-text)" are added to 'Configuration Text' field in the reports (Devices and Configurations search).
There may be challenges with enabling this feature
- Users might have disabled the full-text indexing feature on the db servers.
- This feature is not enabled by default in patches. Some users may not know about this feature.
- If a user has a huge amount of configuration data, it will take time for the first time index to complete and will require some NA down time. Users may have to do additional pruning tasks to eliminate unneeded records.
- As with all new technology, users will have lots of questions.
- This feature is only available on SqlServer and Oracle. It is not supported for customers with MySQL.
There have been new changes in recent versions of full text searching, paricularly when enabling full-text
- Search operators are added to UI by default.
- Users still need to run CLI to do full-text indexing after install or upgrade.
- Upgrade : If full-text is already enabled before upgrade, my best recommendation is to disable it and after patch upgrade or any installation process it can be enabled.
- Use the analyzeindex option to estimate the space and time required to create the index.
- Added the ability to use multiple threads when creating the index.
- Improved internal algorithm, to filter unwanted records as part of indexing.
Algorithms – How it works
When ‘contains’ operator is used, sql 'LIKE' is used in the search SELECT statement.
for example:
select deviceid from rn_device_data where devicedata like ‘%interface%’
When ‘contains (full-text)’ operator is used, sql 'CONTAINS' is used in the search SELECT statement.
for example
select deviceid from rn_device_data where contains (devicedata ,‘interface’)
For CONTAINS to work, the devicedata column must be full-text indexed.
Searching using 'full-text' feature
Select ‘contains(full-text)’ options while searching in configuration text.
‘contains(full-text)’ option search is case insensitive on both Oracle and MSSQL
Search single word examples: interface , telnet,
Examples;
Searching single word with wild card;
Only supported wildcard is ‘*‘
Note: Only trailing wildcards are allowed.
Any wildcard placed at the beginning of a word or within a word is ignored.
Valid example: interf* , tel*
Note: *net, in*ce, would result in UI validation error.
Searching for IP address.
The criteria is the same as searching for single word.
Valid examples:
10.11.12.13 results in exact match.
10.11.12.* results in all address which start with 10.11.12
10.11.12* results in all address which start with 10.11.12
10.11.* results in all address which start with 10.11
10.11.*.13 : not supported
*.11.12.13 : not supported
IPV6 address is also supported and treated as single word.
Searching for a sentence: examples: set vlan, set vpn name
Searching for a sentence with wild cards
Only supported wildcard ‘*‘
White space on either side of ‘*’. If white space is absent it would take part of the word.
Examples:
set * name, telnet * table * settings
Set * name * ( trailing wild card would be ignored)
Logical operators are not supported and considered as part of search string.
AND, OR, NOT, ACCUM, EQUIV