topic Re: Specify field delimiter for Powershell input in Getting Data In

Specify field delimiter for Powershell input

axo959 — Fri, 14 Mar 2014 22:11:59 GMT

I have the following entry in my local input.conf file.

[script://.\bin\execPS.cmd zDBA_AAG_Server.ps1]
source = Powershell
sourcetype = testType
interval = 10
index = mssql

The contents of the batch wrapper execPS.cmd file:

@ECHO OFF
SET MYSPLUNKAPP=Splunk_TA_windows
SET SPLUNK_HOME="C:\Program Files\SplunkUniversalForwarder"
Powershell -command ". '%SPLUNK_HOME%\etc\apps\%MYSPLUNKAPP%\bin\powershell\%1'"

The output is 7 fields and pipe delimited. Which Splunk figures out the first half of each event/row/record. The fourth (and seventh) field has d.hh:mm:ss and I think this throws Splunk off because when I search the index, the events have no data in fields 4-7. Just the pipes.

Server01|10.10.1.10|Windows 2012|.::||||.::

How do I explicitly define the pipe char as the field delimiter?

I've tried these two entries in the transforms.conf file, but neither seemed to work.

[Powershell]
DELIMS="|"
FIELDS="Col1","Col2","Col3","Col4","Col5","Col6","Col7"

and

[testType]
DELIMS="|"
FIELDS="Col1","Col2","Col3","Col4","Col5","Col6","Col7"

Thanks

Re: Specify field delimiter for Powershell input

linu1988 — Fri, 14 Mar 2014 22:57:40 GMT

i doubt the scripts returning any value.. execute it amnually or have a txt output in local dir to see the output.

Re: Specify field delimiter for Powershell input

sbrant_splunk — Sat, 15 Mar 2014 16:54:46 GMT

You'll need to reference these transforms in props.conf. Check out REPORT- in the props.conf doc for details.

Re: Specify field delimiter for Powershell input

axo959 — Tue, 18 Mar 2014 16:40:44 GMT

The script outputs to console or file if redirected.

I don't want to redirect stdout to a file just to ingest that. I'd like to avoid writing to disk twice.

Re: Specify field delimiter for Powershell input

axo959 — Tue, 18 Mar 2014 17:06:05 GMT

I was looking at a db input example to try to understand how the inputs.conf, props.conf and transforms.conf files work together. Obviously my input is different because its a powershell script.

http://docs.splunk.com/Documentation/Splunk/6.0.2/AdvancedDev/ScriptSetup

From the example, I don't see how the 3 files are related. It looks like the fields are defined in the transforms.conf file. I did not see any attributes in the props.conf documentation that seemed related to defining fields.

From the doc:

Configure scripted data input in $SPLUNK_HOME/etc//default/inputs.conf. Use the local directory for the app to overwrite behavior defined in the default directory. Here is an example:

[script://$SPLUNK_HOME/etc/apps/<appName>/bin/starter_script.sh]
disabled = true # change to false to start the input, requires restart
host = # enter hostname here
index = main
interval = 30    #frequency to run the script, in seconds
source = my_db
sourcetype = my_db_data

$SPLUNK_HOME/etc/system/local/props.conf
Configure properties for the script in the Splunk system props.conf:

[my_db]
TIME_PREFIX=^[^\|]+\|
TIME_FORMAT=%Q
MAX_TIMESTAMP_LOOKAHEAD=10  #look ahead 10 characters
SHOULD_LINEMERGE=false

$SPLUNK_HOME/etc/system/local/transforms.conf
Define field transforms in transforms.conf:

[my_db_extractions]
DELIMS = "|"
FIELDS ="EventID","AlertTime","UserName",. . ."

Re: Specify field delimiter for Powershell input

sbrant_splunk — Wed, 19 Mar 2014 22:08:35 GMT

The props.conf example, that you're showing here, only includes what I would refer to as onboarding config (pre-index). That is linebreaking, date/time extraction, time format, etc. There are also search-time configurations that go into props.conf, such as EXTRACT, where you can specify a regex right in the props file.

You can also reference configurations that reside in transforms.conf, such as with REPORT. My point with the original post is that just having a transforms.conf file doesn't do anything, you have to reference the configs that reside in it from props.conf.

Example:

--- props.conf ---

[my_db]
REPORT-db_extractions = my_db_extractions

--- transforms.conf ---

[my_db_extractions]
DELIMS = "|"
FIELDS ="EventID","AlertTime","UserName",. . ."

Another thing to consider, if you have control over the powershell script, is to output the results of the powershell script in key=value pairs. Splunk will automatically extract key=value pairs. This is more dynamic, in the event that the number of fields changes in your output. No transforms.conf necessary.

Re: Specify field delimiter for Powershell input

axo959 — Fri, 21 Mar 2014 21:15:04 GMT

Yeah, that is the part that was missing (for me) in the example. I didn't see how the files were related/linked.

I only have access to the server being monitored. I can only make changes to the universal forwarder. From what I understand, to use what you suggested needs to happen on a search head? Is that correct?

I do have control over the PS1 script. I like not using a transforms.conf idea better. I tried key=value pairs. But same issue. What is the syntax?

Here is sample output to PowerShell console:

host=server1|ip=10.1.1.100|status=Online|uptime=156.1:10:26|sql=DEFAULT|sqlService=MSSQLSERVER|sqlStatus=Running|sqlUptime=156.1:8:45

Here is how that looks in splunk search (its taking each record and making two events now, was one event before. either way, missing same data):

host=server1|IP=10.1.1.100|Status=Online|Uptime=.::|
sql=|sqlService=|sqlStatus=|sqlUptime=.::

Here is the PS1 code that assigns all my variables to one and then prints it. How can splunk see 'sql=' and 'sqlUptime=' but not the variable value?? It works to console and to file.

$toSplunk="host=$hostname|IP=$ipAddress|Status=$hostStatus|Uptime=$hostUptime|sql=$instanceName|sqlService=$serviceName|sqlStatus=$serviceStatus|sqlUptime=$sqlUptime"
$toSplunk

Re: Specify field delimiter for Powershell input

sbrant_splunk — Fri, 21 Mar 2014 21:54:59 GMT

The props/transforms would go on the search head for field extractions but you wouldn't need that if going the key=value route.

Can you modify your script to place the values in quotes? so, for instance, sqlUptime="156.1:8:45"

Re: Specify field delimiter for Powershell input

axo959 — Mon, 24 Mar 2014 20:33:30 GMT

Still wont show the key-value pairs' value
(just listing last two columms)

PS1 stdout using comma separated list of columns
sqlStatus="Running",sqlUptime="158.16:52:6"

Splunk search looks like
sqlStatus="",sqlUptime=".::"

PS1 stdout using comma and space-char
sqlStatus="Running",sqlUptime="158.16:55:2"

Splunk search looks like
sqlStatus="", sqlUptime=".::"