topic Re: Need help with grouping an counting. in Splunk Search

Need help with grouping an counting.

rajivchadha — Tue, 29 Sep 2020 12:23:23 GMT

Hi I have the below event output in the a log.

2016-11-03 17:59:02,943 INFO [SerialClientScheduler-1] c.b.t.m.s.MarketDataServer [?:?] Subscribe for SubscriptionDetails(subscriptions=[BloombergSubscription(identifier=//blp/mktdata/ticker/VOD LN Equity, correlationId=User: 2, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='VOD LN Equity'}), BloombergSubscription(identifier=//blp/mktdata/sedol/0912332 LN, correlationId=User: 3, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=SEDOL, symbol='0912332', otherExchangeCode='XLON', bloombergExchangeCode='LN'}), BloombergSubscription(identifier=//blp/mktdata/sedol/5529027 GY, correlationId=User: 4, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=SEDOL, symbol='5529027', otherExchangeCode='XETR', bloombergExchangeCode='GY'}), BloombergSubscription(identifier=//blp/mktdata/sedol/4699330 FP, correlationId=User: 5, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=SEDOL, symbol='4699330', otherExchangeCode='XPAR', bloombergExchangeCode='FP'}), BloombergSubscription(identifier=//blp/mktdata/ticker/SAN SM Equity, correlationId=User: 6, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='SAN SM'}), BloombergSubscription(identifier=//blp/mktdata/ticker/BARC L Equity, correlationId=User: 7, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='BARC L Equity'}), BloombergSubscription(identifier=//blp/mktdata/ticker/TSCO LN Equity, correlationId=User: 8, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='TSCO LN'}), BloombergSubscription(identifier=//blp/mktdata/ticker/ENI IM Equity, correlationId=User: 9, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='ENI IM Equity'})])

I am trying to count the number of times "identifier=" appears in the event. How can do this? I have tried to work with makemv and mvexpand without success.

Also this type of event can appear multiple times in the log and also across different days. Could you provide some guidance on how I can do the same count across multiple events and days.

Re: Need help with grouping an counting.

somesoni2 — Mon, 16 Jan 2017 16:36:14 GMT

Try like this

To get number of times "identifier=" string is found, try like this (adding a field 'identifier_count' with the count in each event)

your base search | rex max_match=0 "(?<identifier_count>identifier\=)"

To get total number of times the "identifier=" is found in all events spanning across multiple days,

your base search with appropriate time range | rex max_match=0 "(?<identifier_count>identifier\=)"  | stats sum(identifier_count) as total_identifier_count

To get total number of times the "identifier=" is found in all events on per day-wise summary,

your base search with appropriate time range | rex max_match=0 "(?<identifier_count>identifier\=)"  | timechart span=1d sum(identifier_count) as total_identifier_count

Re: Need help with grouping an counting.

rajivchadha — Tue, 29 Sep 2020 12:23:29 GMT

Thanks. Will have a go and respond. Is there a also a way to say split the below line into multiple events and then do a count. I could split the line by doing a count on BloombergSubscription(identifier

2016-11-03 17:59:02,943 INFO [SerialClientScheduler-1] c.b.t.m.s.MarketDataServer [?:?] Subscribe for SubscriptionDetails(subscriptions=[

BloombergSubscription(identifier=//blp/mktdata/ticker/VOD LN Equity, correlationId=User: 2, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='VOD LN Equity'}),

BloombergSubscription(identifier=//blp/mktdata/sedol/0912332 LN, correlationId=User: 3, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=SEDOL, symbol='0912332', otherExchangeCode='XLON', bloombergExchangeCode='LN'}),

BloombergSubscription(identifier=//blp/mktdata/sedol/5529027 GY, correlationId=User: 4, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=SEDOL, symbol='5529027', otherExchangeCode='XETR', bloombergExchangeCode='GY'}),

BloombergSubscription(identifier=//blp/mktdata/sedol/4699330 FP, correlationId=User: 5, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=SEDOL, symbol='4699330', otherExchangeCode='XPAR', bloombergExchangeCode='FP'}),

BloombergSubscription(identifier=//blp/mktdata/ticker/SAN SM Equity, correlationId=User: 6, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='SAN SM'}),

BloombergSubscription(identifier=//blp/mktdata/ticker/BARC L Equity, correlationId=User: 7, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='BARC L Equity'}),

BloombergSubscription(identifier=//blp/mktdata/ticker/TSCO LN Equity, correlationId=User: 8, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='TSCO LN'}),

BloombergSubscription(identifier=//blp/mktdata/ticker/ENI IM Equity, correlationId=User: 9, fields=[MD_EVENT_TYPE, MD_SUB_EVENT_TYPE, BID_PRICE, BID_QTY, ASK_PRICE, ASK_QTY, EID, LAST_PRICE, LAST_QTY], callBackId=02c08163-9ce9-4690-a92a-9d3ac0e37c11, bloombergInstrumentSymbolInfo=BloombergInstrumentSymbolInfo{instrumentSymbolType=BLOOMBERG, symbol='ENI IM Equity'})])

Re: Need help with grouping an counting.

hunters_splunk — Mon, 16 Jan 2017 17:05:54 GMT

Hi rajivchadha

You can use the rex command to search for the "identifier=" pattern in your raw events and assign the values after the pattern to a new field named "identifier". Then use mvexpand to convert this multi-value field into multiple individual events for each field value.
Here is an example for your reference:

sourcetype="<your_sourcetype>" | rex field=_raw "identifier=(?<identifier>\S*)\s" max_match=0 | table identifier | mvexpand identifier | stats count

Hope it helps. Thanks!
Hunter

Re: Need help with grouping an counting.

rajivchadha — Tue, 29 Sep 2020 12:23:32 GMT

Thanks. I tried the below and it worked

index="md_dev" ( "Subscribe for") | rex "identifier=(?\S*)\s" max_match=0 | mvexpand identifier | stats count by identifier

I managed to get a table of identifiers with a count.

Another quick question any idea on why the below did not work. I just changed the identifier and renamed it to id.

index="md_dev" ( "Subscribe for") | rex "id=(?\S*)\s" max_match=0 | mvexpand id | stats count by id

Re: Need help with grouping an counting.

hunters_splunk — Mon, 16 Jan 2017 18:55:09 GMT

Hi rajivchadha,

When you use the rex command, you must specify a new field name (in brackets) to which to assign the regex-captured group.
For example, extract "from" and "to" fields using regular expressions. If a raw event contains "From: Susan To: Bob", then from=Susan and to=Bob.
... | rex field=_raw "From: (?.) To: (?.)"
For more information, please refer to documentation:
http://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/Rex

Thanks!

Re: Need help with grouping an counting.

rajivchadha — Tue, 17 Jan 2017 08:30:46 GMT

The tips were very useful and help me achieve what I needed. Thanks

Re: Need help with grouping an counting.

rajivchadha — Tue, 17 Jan 2017 08:31:11 GMT

understood. thanks