Problem when opening the membership dialogue for some device groups: “[BAD-GRAMMAR] missing numeric value in expression”

  • KM1457142
  • 16-Aug-2012
  • 25-May-2021

Archived Content: This information is no longer maintained and is provided "as is" for your convenience.

Summary

The article treats an error message encounterd when trying to open the membership dialogue for some device groups in SA

Error

The following error is encountered when trying to open the membership dialogue for some device groups in Server Automation (SA).

[BAD-GRAMMAR] missing numeric value in expression

Cause

From the SA Java Client (aka NGUI) fine level logs, when user opens device group membership rules view, the filter string that gets created has device_group_name instead of device_group_id. Same filter string is passed on to twist which ignores the value because it is a varchar and not a number. Hence the sql exception.

 So the issue is in creating filter string at ngui level. See following excerpt from logs:

     FINE in toFilter: userValue AFTER escapeAppropriateValues: "GERMANY"

     FINE (custom_attr_key IN ["EV_ENVIRONMENT_PROD"] )&(device_group_id IN ["GERMANY"] )

When membership view is opened, ngui loads all possible (default value 1000) device_group_ids, user selected device_group_id and merges them and finally does a query to retrieve servers. It seems user selected device_group_id is not in 1000 list and hence a default text value is returned. This lookup threshold value is configurable and can be set from ngui options.

Fix

Please update the following ngui option and see if the issue still occurs:

     ngui -> Tools -> Options -> General -> 'Equals' Operator Limit in Search and Reports

Set value to 2000 or a custom value according to the number of Dynamic Device Groups in your environment.

Important note:

You can use the attached tool (searchable_test.py*) to identify whether the group ID is not in the first 1000 groups:

/opt/opsware/bin/python2 searchable_test.py <user> <password> 1000 > /tmp/searchable_test.txt

If the ID is found here, most likely the root cause is different.