topic Re: How to extract rex value from SQL and charting? in Splunk Search

How to extract rex value from SQL and charting?

cdowlathram — Fri, 10 Jun 2022 19:01:37 GMT

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

Re: Extracting Rex value from SQL and charting

somesoni2 — Fri, 10 Jun 2022 18:40:49 GMT

Give this a try

Re: How to extract rex value from SQL and charting?

cdowlathram — Fri, 10 Jun 2022 23:16:26 GMT

@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

Re: How to extract rex value from SQL and charting?

ITWhisperer — Sat, 11 Jun 2022 08:47:50 GMT

Try something like this

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

Re: How to extract rex value from SQL and charting?

cdowlathram — Mon, 13 Jun 2022 18:30:07 GMT

@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 ?

Re: How to extract rex value from SQL and charting?

ITWhisperer — Mon, 13 Jun 2022 22:21:56 GMT

Does this work for you?

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

Re: How to extract rex value from SQL and charting?

cdowlathram — Mon, 13 Jun 2022 23:58:45 GMT

@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 */" ?.

Re: How to extract rex value from SQL and charting?

cdowlathram — Tue, 14 Jun 2022 00:48:48 GMT

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 !

Re: How to extract rex value from SQL and charting?

ITWhisperer — Tue, 14 Jun 2022 05:19:54 GMT

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.