Brtdku

First time poster and still learning the ropes, so I apologize if the description below is overly verbose.

I have a database of usage logs I'm pulling data from via various pre-parsed fields. This query is intended to return the count of how many times a distinct error signature was logged over a given period of time. Each logged error is assigned a signature_id, errors of the same type are all assigned the same signature_id. One of the fields I'm returning in my query, message, returns the entire message stack trace/block of usage log text.

I want my query to group by signature_id, which is a pre-parsed field in the table I'm selecting from. I'm struggling to make it work because, while similar error types are assigned the same signature_id', every usage log differs slightly due to the timestamp of when the message was logged. So my query is grouping bymessageinstead ofsignature`.

EX: Of what my query returns if I return the entire usage log message

signature_id    

b2dea422 



message

2019-01-17 18:01:52,130 ip-BLANK [WARN ][159] [request_id=00e74d7c] Type=Blank.Multiverse.Web.Mvc.Attributes.Usage+UsageLoggingException Message=UsageLogContext not present in HttpContext.Current.Items Data: Signature=b2dea422 Stack Trace: at Blank.Blank.Web.Mvc.Attributes.Usage.GetUsageLogContext() at Blank.Blank.Web.Mvc.Attributes.Usage.AddData(Object data)



count

1



signature_id    

b2dea422 



message

2019-01-17 16:21:36,681,130 ip-BLANK [WARN ][38] [request_id=c140f8ea] Type=Blank.Multiverse.Web.Mvc.Attributes.Usage+UsageLoggingException Message=UsageLogContext not present in HttpContext.Current.Items Data: Signature=b2dea422 Stack Trace: at Blank.Blank.Web.Mvc.Attributes.Usage.GetUsageLogContext() at Blank.Blank.Web.Mvc.Attributes.Usage.AddData(Object data)



count

1

I mentioned above that every usage log differs due to the timestamp of when a given message was logged, but similar error types are assigned the same signature_id. Similar error types also share the same Exception Message=...

EX: Every time a message is logged with signature_id=ab7d890pq, it will also have Exception Message=Cannot read property 'get' of undefined in the message block.

Since the table I'm selecting from doesn't have a pre-parsed exception_message field, I want to parse out the Exception Message= string so my GROUP BY will return the count of distinct logged signature_id's and a column with the exception message is for each distinct signature.

My current query shown below begins to parse out the exceptionmessgage string, but I can't get it to return the entire string:

SELECT CASE

           WHEN sourcecategory = 'source_hello_world_category' THEN 'hwCategory'

       END AS Service,

       signature,

       NULLIF(SUBSTRING(REGEXP_SUBSTR(message, 'Message=\w+[[:space:]]+'), 9), '') AS exceptionmessage,

       count(*)

FROM user_usage_logs

WHERE (signature IS NOT NULL

       AND signature NOT IN ('ccce9e73',

                             '787dd1b5',

                             '17fc66bc',

                             'ca384d1f',

                             '20121ecb',

                             'ccce9e73'))

  AND sourcecategory IN ('source_hello_world_category')

  AND messagetime > (getdate() - 1)

GROUP BY signature,

         sourcecategory,

         exceptionmessage

ORDER BY COUNT DESC

LIMIT 10;

The code shown above returns:

signature_id   exceptionmessage        count



b1det422       Cannot                  31,321

330ope77       Unauthorized            1,207

53m6m466       Reference               311

This is an example of I want returned:

signature_id   exceptionmessage                                  count



b1det422       Cannot read property 'get' of undefined Stack     31,321

330ope77       Unauthorized access response for many users       1,207

53m6m466       Reference cannot be set to an empty.object.3      311