topic Re: Converting a sql case statement into different events in Splunk Search

Converting a sql case statement into different events

swilson91 — Wed, 20 Mar 2013 17:35:00 GMT

Hi,

I'm hoping someone can help me I currently have some queries I run that I can looking to automate into Splunk. One of them in particular involves a case statement that has different outcomes when I change this to run in Splunk it puts it all onto one line treating it as one event is there any way to display this in splunk? Some sample data is below:

Case speed strength weight height
Person 1 100 130 70 50
Person 2 120 100 80 55
Person 3 150 150 80 60
Person 4 70 90 70 65
Person 5 60 30 90 70
Person 6 20 100 100 75

So for the sample output of this query in splunk key pair values would be like:

Wed Mar 20 14:00:01 GMT 2013 Case="Person1"speed="100"strength="130"weight="70"height="50" Case="Person2"speed="120"strength="100"weight="80"height="55"

I now want to search this in splunk and compare the values of each person together i.e speed vs speed etc.

Is this possible? Any help would be appreciated

Re: Converting a sql case statement into different events

lguinn2 — Wed, 20 Mar 2013 19:14:54 GMT

I am not sure that I understand your question. So I will rephrase it like this:

"I loaded this table into Splunk, but Splunk put all the data into a single event. I need to treat each line as a separate event."

First, I suggest that you ask Splunk to break this data up when it is brought into Splunk. You can do this with the configuration file props.conf Put it on your indexer in $SPLUNK_HOME/etc/system/local. (You can also put it in the same directory as your inputs.conf if you are not using a forwarder).

[source::/your/file/name/here]
SHOULD_LINEMERGE = false
DATETIME_CONFIG = NONE

Note that this configuration is NOT retroactive, so you will need to remove and re-index this file.

Once you have done that, it should be a simple matter to set up the fields (I suggest the Interactive Field Extractor). After that, you will be able to run statistics, etc.

source=/your/file/name/here
| table Case speed   strength  weight  height

Re: Converting a sql case statement into different events

swilson91 — Thu, 21 Mar 2013 09:34:36 GMT

Hi thanks for the response. Basically I query a database a get an output similar to the one above. I used to graph it in excel and compared speed, strength etc on seperate graphs but the way that Splunk logs it is difficult to plot all the strength values on one graph, speed on one graph etc.