How to extract rex value from SQL and charting?

[cdowlathram]

From this given log:

"SQL:SELECT TABLE_NAME, COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME = ? AND TABLE_NAME in (?,?,?,?,?,?,?) AND DATA_TYPE_NAME IN ('CLOB', 'NCLOB', 'BLOB')","i":1,"t":250,"slft":250,"st":250,"m":16,"nr":0,"rt":0,"rn":8,"fs":0}

1. I want to extract the entire SQL's containing table names "TABLE_COLUMNS"   .

2. Extract their corresponding  numbers for t , slft  

3. chart on:  SQL_STMT | t | slft 

I need some help to get this query working: 

"SELECT TABLE_NAME, COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME"
| rex field= _raw "\"SQL:(?P<SQL_stmt>)\s*[FROM TABLE_COLUMNS]\s+\""
| rex field=_raw "SELECT \s*  FROM TABLE_COLUMNS \s* ,\"t\":(?P<tvalue>[\d]) "slft":?P<slft_value>\d"| chart count over by SQL_stmt,tvalue, slft_value | sort  by slft_value desc

[cdowlathram · Accepted solution]

Finally this worked :

"TABLE_COLUMNS"
| rex "\"SQL:(?<SQL_src>[^}]+)}"
| rex field=SQL_src "(?<SQLstmt>[^\"]+)\""
| rex field=SQL_src ".+\sfrom\s(?<tableName>\S+)"
| rex field=SQL_src "\"t\"\:(?<tValue>\d+)"
| rex field=SQL_src "\"i\"\:(?<iValue>\d+)"
| where match(tableName,"(?i)table*")
| stats count(iValue) as iValue by SQLstmt,tableName,SQLT,tValue
| sort by tValue desc

Thanks @ITWhisperer  and @somesoni2  !

[ITWhisperer]

Try something like this

| rex "\"SQL:(?<SQL_stmt>[^\"]+)\""
| rex "\"t\":(?<t>[\d]+)\.*\"slft\"\:(?<slft>\d+),"

[cdowlathram]

@ITWhisperer  - Thanks for your response .  The SQL stmt gets populated now , however the following SQL stmt and its corresponding  slft value does not gets captured.

"SQL:SELECT TABLE_NAME, COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME = ? AND TABLE_NAME in (?,?,?,?,?,?,?) AND DATA_TYPE_NAME IN (#2)/* BAD_SQL_WITH_LITERAL */","i":1,"t":410,"slft":410,"st":410,"m":54,"nr":0,"rt":0,"rn":8,"fs":0},

How do I include this boundary condition ? : /* BAD_SQL_WITH_LITERAL */ 

I tried this :  rex "\"SQL:(?<SQL_stmt>[\/\* BAD_SQL_WITH_LITERAL \*\//^\"]+)\"" 

but no luck yet. What am I missing here ?

[ITWhisperer]

Does this work for you?

| rex "\"SQL:(?<SQL_stmt>[^\"\/]+)"

[cdowlathram]

@ITWhisperer - Yes it does, but not for this event : 

SQL:SELECT TABLE_NAME, COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME = ? AND TABLE_NAME in (?,?,?,?,?,?,?) AND DATA_TYPE_NAME IN (#2)/* BAD_SQL_WITH_LITERAL */","i":1,"t":410,"slft":410,"st":410,"m":54,"nr":0,"rt":0,"rn":8,"fs":0},

The querry does not work for the following event as well :

{"n":"SQL: select column_name from table_columns where table_name = ? and schema_name = pkg_tool_get_user() ; Params: (1:A_NT_EVTHT, ); Method name:execute","i":1,"t":279}

I had modified the query like so : 

"SELECT TABLE_NAME, COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME"
| rex   "\"SQL:(?<SQL_src>[^}]+)}"
| rex  field=SQL_src "(?<SQLstmt>[^\"]+)\""
| rex field=SQL_src ".+\sFROM\s(?<tableName>\S+)"
| rex field=SQL_src "\"t\"\:(?<tValue>\d+)"
| rex field=SQL_src "\"i\"\:(?<iValue>\d+)"
| search tableName=*
| stats count(iValue) as iValue by SQLstmt,tableName,SQLT,tValue
| sort by tValue desc

The  field ```SQL_src``` does not capture the above mentioned event . Looks like I need to include  validation for boundary /* BAD_SQL_WITH_LITERAL */". 

But it works fine for the other cases.  How do I escape the */"  ?.  

[ITWhisperer]

I am not sure I understand - do you want the /* .... */ included in SQLstmt? Also, what doesn't work about the other statement?

Please clarify your complete requirement.

[cdowlathram]

@somesoni2  - Thanks for your input !  Two things:

1.  I see the SQL_stmt does not capture the entire SQL statement , instead it captures "tableName" . So I set it to capture the table name. I'm still unable to capture the SQL_stmt.  I set the right boundary of the  SQL_stmt with a \", but still no luck.   Could you please help me with this .

2. The tValue and slftValue does not capture the values corresponding to  the pattern :

 "SQL:SELECT TABLE_NAME, COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME = ? AND TABLE_NAME in (?,?,?,?,?,?,?) AND DATA_TYPE_NAME IN ('CLOB', 'NCLOB','BLOB')","i":1,"t":250,"slft":250,"st":250,"m":16,"nr":0,"rt":0,"rn":8,"fs":0}

Instead it has a value from a different table and not TABLE_COLUMN 

I tried this , no luck :

"SELECT TABLE_NAME, COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME"
| rex "SQL\:SELECT TABLE_NAME, COLUMN_NAME FROM TABLE_COLUMNS \s* \"t\"\:(?<tValue>\d+)"
| stats count by SQL_stmt tValue 
| sort by slft_value desc

[somesoni2]

Give this a try

your base search fetching SQL logs
| rex "SQL\:.+\sFROM\s(?<SQL_stmt>\S+)"
| rex "\"t\"\:(?<tValue>\d+)"
| rex "\"slft\"\:(?<slftValue>\d+)"
| stats count by SQL_stmt tValue slftValue
| sort slft_value desc