Splunk Search

Correctly Script SQL Queries

danurag
Explorer

Hi I have a batch file that executes a sqlserver query using sqlcmd.

The contents of the batch file are:

sqlcmd -i query.sql -X -h -1

The query.sql contains

set nocount on
go
select "time=" + convert(varchar,getdate(),121)+"    count="+cast(count(0) as varchar)
from abc;
go

The output of the query looks like
time=2011-01-06 11:30:57.533 count=56

Now, I would like to create 2 fields, time and count, and then be able to plot them in a chart or develop alerts on them based on rate of change or last value above threshold.

Questions: a. How should I change the query to make it easier for Splunk to create 2 fields? b. I will be running lot of different queries to look into the data. What is the best way to do this? c. How do I tell Splunk to use the time as the timestamp?

Thank you.

1 Solution

ftk
Motivator

a) I would change the query to output XML, which is easy for splunk to extract fields from. I'll post an example below.
b) You can probably consolidate your queries, or just create an input for each.
c) Here is the doc section on extracting timestamps. In your example you would want to use TIME_PREFIX = time=. Since you are using the current time in your SQL query for time= you might as well remove this from your query and just have splunk use the timestamp of when the script was run as the event timestamp by using DATETIME_CONFIG = CURRENT in props.conf.

Here is how I use sqlcmd.exe to pull data from MSSQL:
In my inputs.conf I define the script:

[script://$SPLUNK_HOME\etc\apps\test\bin\sqlrun-dbsize.cmd]
interval = 600
sourcetype = sqlrun

And put the appropriate script in the bin folder (sqlrun-dbsize.cmd😞

@sqlcmd.exe -h-1 -y0 -X -i "%SPLUNK_HOME%\etc\apps\test\bin\dbsize.sql"

this script will call sqlcmd.exe in XML mode and run dbsize.sql. dbsize.sql contains my SQL query that outputs XML:

SET NOCOUNT ON
:XML ON

SELECT
    CASE
        WHEN SERVERPROPERTY ('instance') IS NULL THEN @@SERVERNAME
        ELSE SERVERPROPERTY ('instance')
    END AS 'instance',
    DB_NAME(dbid) AS [database],
    CONVERT(DEC(15), SUM(size))* 8192/ 1048576 AS [database_size] FROM sys.sysaltfiles
GROUP BY dbid
ORDER BY 2 DESC

FOR XML RAW, ELEMENTS

Then in props.conf I define the sqlrun sourcetype. Note that I set the event timestamp to the time the script is run with DATETIME_CONFIG. I also define the linebreaker and use a custom xmlkv extraction (defined in transforms.conf) because I have run into glitches with the splunk internal xmlkv extractions.

[sqlrun]
DATETIME_CONFIG = CURRENT
SHOULD_LINEMERGE = False
LINE_BREAKER = (</row>)
REPORT-xmlkv = xmlkv-extraction

XML extractions in transforms.conf:

[xmlkv-extraction]
MV_ADD = True
REPEAT_MATCH = True
REGEX = <([^\s\>]*)[^\>]*\>([^<]*)\<\/\1\>
FORMAT = $1::$2

This setup will create individual events for every row of output of the sql query. Here is a sample event:

<instance>SERVERNAME</instance><database>master</database><database_size>4.50000000</database_size>

Splunk extracts the key value pairs such as instance, database, and database_size automatically.

You should be able to use this as an example of how to set up sqlrun scripts. If you run into any problems feel free to leave a comment.

View solution in original post

NiklasB
Explorer

I built an SQL query to load up all recently added cases from your CRM system, to be able to plot case activity in Splunk. I can tell you that it feels very very hard to get linebreaking to work properly for batched data output by custom scripts for some reason. 3 hours of trying, using all the tricks in the book, to no avail. I even went as far as to have my sqlcmd operation output newline separated items, but still proper linebreaking seem to refuse to occur (eventhough that is supposedly the default linebreaker).

Ended up rewriting my SQL queries to output only one row per execution and thereafter tell Splunk to invoke the script once a second instead of the originally intended once a minute.... May work for some people.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

I would actually not use XML, since compared with KV extraction, it is expensive and unnecessary for single-valued data. Instead, simply output the fields as KV pairs, either single or multiline as appropriate described in my (or ftk's) answer here: http://answers.splunk.com/questions/3231/escaping-characters-in-an-event/3549#3549. Note how I recommend outputting the timestamp a little differently, and the configs for interpreting/extracting the output.

ftk
Motivator

While I do agree on not using XML generally, when working with sqlcmd.exe it makes things a whole lot easier. Reason being that the output sqlcmd produces is great for human eyes, but not very consistent, especially when it comes to multi line results. When setting sqlcmd.exe to output XML one can expect the same format no matter what the char length of column headers or results is.

0 Karma

ftk
Motivator

a) I would change the query to output XML, which is easy for splunk to extract fields from. I'll post an example below.
b) You can probably consolidate your queries, or just create an input for each.
c) Here is the doc section on extracting timestamps. In your example you would want to use TIME_PREFIX = time=. Since you are using the current time in your SQL query for time= you might as well remove this from your query and just have splunk use the timestamp of when the script was run as the event timestamp by using DATETIME_CONFIG = CURRENT in props.conf.

Here is how I use sqlcmd.exe to pull data from MSSQL:
In my inputs.conf I define the script:

[script://$SPLUNK_HOME\etc\apps\test\bin\sqlrun-dbsize.cmd]
interval = 600
sourcetype = sqlrun

And put the appropriate script in the bin folder (sqlrun-dbsize.cmd😞

@sqlcmd.exe -h-1 -y0 -X -i "%SPLUNK_HOME%\etc\apps\test\bin\dbsize.sql"

this script will call sqlcmd.exe in XML mode and run dbsize.sql. dbsize.sql contains my SQL query that outputs XML:

SET NOCOUNT ON
:XML ON

SELECT
    CASE
        WHEN SERVERPROPERTY ('instance') IS NULL THEN @@SERVERNAME
        ELSE SERVERPROPERTY ('instance')
    END AS 'instance',
    DB_NAME(dbid) AS [database],
    CONVERT(DEC(15), SUM(size))* 8192/ 1048576 AS [database_size] FROM sys.sysaltfiles
GROUP BY dbid
ORDER BY 2 DESC

FOR XML RAW, ELEMENTS

Then in props.conf I define the sqlrun sourcetype. Note that I set the event timestamp to the time the script is run with DATETIME_CONFIG. I also define the linebreaker and use a custom xmlkv extraction (defined in transforms.conf) because I have run into glitches with the splunk internal xmlkv extractions.

[sqlrun]
DATETIME_CONFIG = CURRENT
SHOULD_LINEMERGE = False
LINE_BREAKER = (</row>)
REPORT-xmlkv = xmlkv-extraction

XML extractions in transforms.conf:

[xmlkv-extraction]
MV_ADD = True
REPEAT_MATCH = True
REGEX = <([^\s\>]*)[^\>]*\>([^<]*)\<\/\1\>
FORMAT = $1::$2

This setup will create individual events for every row of output of the sql query. Here is a sample event:

<instance>SERVERNAME</instance><database>master</database><database_size>4.50000000</database_size>

Splunk extracts the key value pairs such as instance, database, and database_size automatically.

You should be able to use this as an example of how to set up sqlrun scripts. If you run into any problems feel free to leave a comment.

danurag
Explorer

Got this to work. Had to move files into app/local

0 Karma

ftk
Motivator

So you have kept the inputs, props, and transforms.conf as in my example? Did you restart your splunk instance after putting them in place? When you do a search for source "blah" do you see the appropriate fields being extracted in the field picker to the left?

0 Karma

danurag
Explorer

I have created a new app and in the app/default folder, I have kept inputs.conf, props.conf and transforms.com. For some reason, it is not doing the transformation.

I can see the data like 2 but
when I try source "blah" | stats avg(occurrence), it tells me "Specified field(s) missing from results: 'occurrence'

Also it says, found 13 matching events.

Please help.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...