Logstash extracting values from sp_executesql

Question

We're tracking and shipping our SQL Server procedure timeouts into Elasticsearch, so we can visualize them in Kibana in order to spot issues.

Some of our SQL queries are parameterized and use sp_executesql.

Would it be possible to extract its parameters and their values from query?

For instance:

EXEC sp_executesql N'EXEC dbo.MySearchProcedure @UserId=@p0,@SearchPhrase=@p1'
    , N'@p0 int,@p1 nvarchar(max)'
    , @p0 = 11111
    , @p1 = N'denmark';

And get this result out of it:

{
  "Procedure": "dbo.MySearchProcedure",
  "Statement": "exec sp_executesql N'exec Search.GetAnalysisResultsListTextSearch @SubscriberId=@p0,@SearchTerms=@p1,@SortType=@p2',N'@p0 int,@p1 nvarchar(max) ,@p2 int',@p0=47594,@p1=N'denmark',@p2=0",
  "Parameters": {
    "UserId": 11111,
    "SearchPhrase": "denmark"
  }
}

Alain Collins · Accepted Answer · 2015-12-07 20:26:46Z

1

Sounds like a job for the ruby{} filter. First, locate all your key=value pair in the query (@userid=@p0, probably using ruby's scan feature), then locate the assignments (@p0=1234, using scan again), then create a new field combining the two (userid=1234). In the ruby filter:

 event['userid'] = '1234'

answered Dec 7, 2015 at 20:26

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Evaldas Buinauskas Over a year ago

I thought about this. Looks like there's nothing easier. Thank you.

Collectives™ on Stack Overflow

Logstash extracting values from sp_executesql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related