Dashboards & Visualizations

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

Ask_2020
Loves-to-Learn Lots

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. 

Splunk xy.PNG

 

Thanks for the help in advance.

 

Labels (1)
0 Karma

twesty
Path Finder

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. 

0 Karma

Ask_2020
Loves-to-Learn Lots

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.  

Splunk xy.PNG

 

Thanks for the help. 

0 Karma

Ask_2020
Loves-to-Learn Lots

Can you please help me ? @twesty 

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...