CODEWITHSUNDEEP
elasticsearchlogstash
Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Logstash extracting values from sp_executesql

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"
  }
}

Upvotes: 0

Views: 45

Answers (1)

Alain Collins
Alain Collins

Reputation: 16362

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'

Upvotes: 1

Related Questions