Key Value during search

[gudavasr]

How can this be split into key-value during search?
||| --> this marks seperation between key and value
for ex: key is TRADE_VALUATION
value is 9926878 EUR ValDate 9/30/13 11:59:59.999 PM EDT
XXX --> marks start of different trade

TRADE_VALUATION 9926878 EUR ValDate 9/30/13 11:59:59.999 PM EDT ||| NPV (493,855.68) EUR ||| ACCRUAL (-76,527.50) EUR ||| CASH (0.00) EUR ||| NOTIONAL_CURRENT (15,000,000.00) EUR ||| NOTIONAL (15,000,000.00) EUR ||| NPV_MKTING (0.00) USD ||| NPV_PAYLEG (-7,806,133.77) EUR ||| NPV_RECLEG (8,299,989.45) EUR ||| FIXED01 (-32,689.00) EUR ||| PRINCIPAL_EXCHANGE_COUNT (0) EUR XXX Trade_Valuation 10091675 GBP ValDate 9/30/13 11:59:59.999 PM EDT ||| NPV (16,129.78) GBP ||| ACCRUAL (2,755.48) GBP ||| CASH (0.00) GBP ||| NOTIONAL_CURRENT (75,000,000.00) GBP ||| NOTIONAL (75,000,000.00) GBP ||| NPV_MKTING (0.00) USD ||| NPV_PAYLEG (-426,043.90) GBP ||| NPV_RECLEG (442,173.69) GBP ||| FIXED01 (7,469.15) GBP ||| PRINCIPAL_EXCHANGE_COUNT (0) GBP

Thank you.

[lukejadamec]

Install Splunk on a Windows OS.

Create a ‘log’ file that contains the data you posted:

Create exactly: c:\splunktest\trades\trade.log

Paste the following test into your new log file:

TRADE_VALUATION 9926878 EUR ValDate 10/15/13 10:59:59.999 PM EDT ||| NPV (493,855.68) EUR ||| ACCRUAL (-76,527.50) EUR ||| CASH (0.00) EUR ||| NOTIONAL_CURRENT (15,000,000.00) EUR ||| NOTIONAL (15,000,000.00) EUR ||| NPV_MKTING (0.00) USD ||| NPV_PAYLEG (-7,806,133.77) EUR ||| NPV_RECLEG (8,299,989.45) EUR ||| FIXED01 (-32,689.00) EUR ||| PRINCIPAL_EXCHANGE_COUNT (0) EUR XXX Trade_Valuation 10091675 GBP ValDate 10/15/13 10:59:59.999 PM EDT ||| NPV (16,129.78) GBP ||| ACCRUAL (2,755.48) GBP ||| CASH (0.00) GBP ||| NOTIONAL_CURRENT (75,000,000.00) GBP ||| NOTIONAL (75,000,000.00) GBP ||| NPV_MKTING (0.00) USD ||| NPV_PAYLEG (-426,043.90) GBP ||| NPV_RECLEG (442,173.69) GBP ||| FIXED01 (7,469.15) GBP ||| PRINCIPAL_EXCHANGE_COUNT (0) GBP

Create a new index:

Log into Splunk and from Manager>Indexes>Add New

In Index Name enter ‘market’ without quotes.

Create a new input:

From Windows Explorer open c:\program files\splunk\etc\apps\learned

Create a new folder called ‘local’

In the local folder, create a new file in the folder called ‘inputs.conf’ Note: Verify with Properties that the file extension is .conf not .txt

Add the following text (case sensitive) to your new inputs.conf and save the file:

[monitor://c:\splunktest\trades\trade.log]
sourcetype = trades
index = market
followTail = 0

Create a new sourcetype:

From Windows Explorer open c:\program files\splunk\etc\apps\learned\local

Create a new file called props.conf Note: Verify with Properties that the file extension is .conf not .txt

Add the following text (case sensitive) to your new props.conf and save the file:

[trades]
LINE_BREAKER = (?msi)\s(XXX)\s
TIME_PREFIX = ValDate\s
MAX_TIMESTAMP_LOOKAHEAD = 32
TIME_FORMAT = %D%t%I:%M:%S.%N%t%p%t%Z

Restart Splunk:

Login to Splunk, from Manager>Server Controls select Restart Splunk and confirm.

Login to Splunk.

Run this search in App>Search, and from the time picker on the right select All Time from the drop down menu.

index=market | rex field=_raw "(?msi)Trade_Valuation\s(?<TradeValuation>\d+)\s(?<TradeValueCurrency>\w+)\s.*" |table _time,TradeValuation,TradeValueCurrency

Note: The time is sensitive to your users default time. To set user time go to Manager > Access Controls > Users > select your user, and set the time zone.

No applause please, just throw money 🙂

[lukejadamec]

By the way, this is the hard way. The extractions are done by hand at search time.
If I get time, I'll see if I can configure Splunk to extract the fields in the background.

[gudavasr]

Thank you; will try this.

[lukejadamec]

Yes, but it would be such a waste of data that most likely would be useful.
Is this data that has already been indexed by Splunk, or is this a 'new' implementation?
It matters because you can either configure the sourcetype at index time to allow simple search queries, or you can do nothing at index time and try to manage complex search queries.

[gudavasr]

or can we split this into key-value in search window:
ACCRUAL (-76,527.50) EUR ||| CASH (0.00) EUR
like:

ACCRUAL - (-76,527.50) EUR
CASH - (0.00) EUR

Thank You.

[lukejadamec]

That is a complex question that appears to be wrong.
||| appears to separate a key value combination not between a key and value. The key appears to be the first block of characters after the ||| or XXX.
In terms of Splunk, XXX starts a new event with its own timestamp, which is a value itself.
The numbers appear to be both with and without () and because they are basically random will not be of use unless they are also extracted as values, which are actually followed by their key.
You might want to hire someone to build an app for this.