Splunk Search

How to extract rex value from SQL and charting?

cdowlathram
Explorer

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

 

Labels (2)
0 Karma
1 Solution

cdowlathram
Explorer

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  !

View solution in original post

0 Karma

cdowlathram
Explorer

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  !

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

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

cdowlathram
Explorer

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

 

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Does this work for you?

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

cdowlathram
Explorer

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

SQL:SELECT TABLE_NAMECOLUMN_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 */"  ?.  

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

cdowlathram
Explorer

@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

 

0 Karma

somesoni2
Revered Legend

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

 

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...