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?
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:
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.
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
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.
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?
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.
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.
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.