TIBCO LogLogic LMI: Effect of Case in Grouping in Advanced Searches
Article ID: KB0077941
Updated On:
| Products | Versions |
|---|---|
| TIBCO LogLogic Enterprise Virtual Appliance | >=6.1.0 |
Description
By default the GROUP BY function is case-sensitive.
For example, consider a data model, myTestModel, that obtains logs from various sources. The data model contains a property called payload. The payload property captures a string that is test in some logs and TEST in others. Using GROUP BY in an Advanced Search separates the uppercase and lowercase versions of the payload.
EQL:
use myTestModel | GROUP BY payload
SQL:
SELECT count(*) FROM myTestModel GROUP BY payload
In order to make the response for the SQL to match that given by EQL (i.e. to include the payload's value with its count) use
SELECT payload, count(*) FROM myTestModel GROUP BY payload
Each of the above will report the total number of logs with test or TEST entries separately. The second SQL query, like the EQL query, will list the number of entries of the payload against the payload's value:
# payload count
1 test 10
2 TEST 10
The separation of the two forms of 'test' may be desirable. However, it may not be.
For example, consider a data model, myTestModel, that obtains logs from various sources. The data model contains a property called payload. The payload property captures a string that is test in some logs and TEST in others. Using GROUP BY in an Advanced Search separates the uppercase and lowercase versions of the payload.
EQL:
use myTestModel | GROUP BY payload
SQL:
SELECT count(*) FROM myTestModel GROUP BY payload
In order to make the response for the SQL to match that given by EQL (i.e. to include the payload's value with its count) use
SELECT payload, count(*) FROM myTestModel GROUP BY payload
Each of the above will report the total number of logs with test or TEST entries separately. The second SQL query, like the EQL query, will list the number of entries of the payload against the payload's value:
# payload count
1 test 10
2 TEST 10
The separation of the two forms of 'test' may be desirable. However, it may not be.
Issue/Introduction
How to make the GROUP BY function in Advanced Searches using EQL and SQL is case-insensitive.
Resolution
If one wishes to combine the uppercase instances and the lowercase instances then the LOWER or UPPER functions can be used. Please be aware that any feature, such as a dashboard widget relying on such a query will also be affected
EQL:
use myTestModel | GROUP BY UPPER(payload)
use myTestModel | GROUP BY LOWER(payload)
SQL:
SELECT count(*) FROM myTestModel GROUP BY UPPER(payload)
SELECT count(*) FROM myTestModel GROUP BY LOWER(payload)
Following the same SQL to include the payload's value with its count use, but this time with the LOWER function, or using EQL, only a single value will be returned.
SELECT payload, count(*) FROM myTestModel GROUP BY LOWER(payload)
Using the lower function with the same data containing the ten values of test and ten values of TEST, but using the lower function the following is returned
# payload count
1 test 20
EQL:
use myTestModel | GROUP BY UPPER(payload)
use myTestModel | GROUP BY LOWER(payload)
SQL:
SELECT count(*) FROM myTestModel GROUP BY UPPER(payload)
SELECT count(*) FROM myTestModel GROUP BY LOWER(payload)
Following the same SQL to include the payload's value with its count use, but this time with the LOWER function, or using EQL, only a single value will be returned.
SELECT payload, count(*) FROM myTestModel GROUP BY LOWER(payload)
Using the lower function with the same data containing the ten values of test and ten values of TEST, but using the lower function the following is returned
# payload count
1 test 20
Feedback
Yes
No
Powered by
