I'm trying to compare SQL results between two databases. using stats and xyseries.

[Ask_2020]

Question:  I'm trying to compare SQL results between two databases using stats and xyseries. Everything is working as expected when we have unique table name in the events but when we have same table name multiple times (3 times) in the events, xyseries is printing the table name only once instead of 3 times. So, how do we print same table name 3 times in the output.  

Below is the query which i'm using.  

(index="ABC_index" source=XX ) OR (index="XYZ_index" source="YY" ) 
| fillnull value=NULL
| eval Env = if(source="yy", "P", "C")
| rename TABLE_NAME as TABLE_NAME
| rename CONSTRAINT_TYPE as CONSTRAINT_TYPE
| rex field=_raw "COLUMN_LIST=\"\s+\((?P<Column_List>\w+)"
| rename R_CONSTRAINT_NAME as R_CONSTRAINT_NAME
| stats count by TABLE_NAME CONSTRAINT_TYPE Column_List Env R_CONSTRAINT_NAME
| xyseries Column_List, Env, TABLE_NAME, Column_List, CONSTRAINT_TYPE R_CONSTRAINT_NAME grouped=TRUE sep="_"
| table TABLE_NAME_C TABLE_NAME_P Column_List_C Column_List_P CONSTRAINT_TYPE_C CONSTRAINT_TYPE_P R_CONSTRAINT_NAME_C R_CONSTRAINT_NAME_P
| fillnull value=NULL

Results attached: 

In the below output, table name is printed only two times and 3rd time is missing in the results. So, how do we display all 3 times. 

Thanks for the help in advance.

[twesty]

I would probably investigate what the output of that stats command is and confirm that you have still the right amount of results for the tables. It wouldnt surprise me if you lose values due to the fact that all the values are the same. Additionally, xyseries will also merge results if they are exactly the same. 

[Ask_2020]

Hi Twesty,

Thanks for the response, 

Please find the stats query below and output for the same. 

Query:

(index="ABC_index" source=XX ) OR (index="XYZ_index" source="YY" )
| fillnull value=NULL
| eval Env = if(source="yy", "PIS", "CIS")
| rename TABLE_NAME as TABLE_NAME
| rename CONSTRAINT_TYPE as CONSTRAINT_TYPE
| rex field=_raw "COLUMN_LIST=\"\s+\((?P<Column_List>\w+)"
| rename R_CONSTRAINT_NAME as R_CONSTRAINT_NAME
| stats count by TABLE_NAME CONSTRAINT_TYPE Column_List Env R_CONSTRAINT_NAME
| sort Env

In the below output  values in "TABLE_NAME" column are same but there are unique values in  CONSTRAINT_TYPE and Column_List columns, that's the reason stat command is giving right amount of results. 

When i use xyseries, I don't see  line 2, line 3 results in Env=CIS and I don't see line 6 and line 7 results in Env=PIS. 

I would like to see same results as my stats command when i use xyseries.  

Thanks for the help. 

[Ask_2020]

Can you please help me ? @twesty