topic Re: How to use rex to parse Select Query in a log? in Splunk Search

How to use rex to parse Select Query in a log?

dkarthik16 — Wed, 06 Jun 2018 11:00:14 GMT

Hi,

I have a log like below

"12","select a.a,b.b,c from a,b where a.a = b.a group by xxxx","impala",2017-06-30T00:59:40

I am trying to parse this to extract where condition for some performance analysis.

I tried this in regex101 and got the below regex

\"\d+\",\"select\s+(.\*)\s+from\s+(.\*)\s*where\s(.\*)\s+group by\s+(.\*)\s*\",(.\*)\s*,(.\*)

Help me to implement it in SPL.

Re: How to use rex to parse Select Query in a log?

jodyfsu — Wed, 06 Jun 2018 13:19:05 GMT

I think you have it worked out for the most part. not seeing an actual example leaves a lot of possible incorrectness, but here is what I think you are after.
| rex "\"\d+\",\"select\s+(.*)\s+from\s+(.*)\s*where\s(?<WHERECAPTURE>.*)\s+group by\s+(.*)\s*\",(.*)\s*,(.*)"

Let us know if you need more.

Re: How to use rex to parse Select Query in a log?

jodyfsu — Wed, 06 Jun 2018 13:22:59 GMT

And, if you are only trying to capture the WHERE:

| rex "where\s(?<WHERECAPTURE>.*)\s"

Re: How to use rex to parse Select Query in a log?

dkarthik16 — Thu, 07 Jun 2018 06:37:43 GMT

Thanks ... And as you mentioned there are some mismatches in actual logs and Since the query was written by Humans which doesn't follow any meaningful pattern. Actually I extended my analysis to extract JOINS also.