Lookup Question

[_gkollias]

My use case is to find out how many transactions went out to a customer for a particular day. The results will include a tie out of counts and dollars from end to end. In some cases receipts go out to both a supplier and customer, so for that transaction I want to be able to see that it went out to both. TPCode is a field that we use in Splunk to differentiate all customers, and I have a lookup that lists the supplier TPCodes along with their customer TPCodes. These particular TPCodes that I want to differentiate are also a specific domain of customers, so simply grouping by TPCode will include other domains.

So, my lookup consists of the the following headers (among others):

supplier_TPCode, customer_TPCode

Initially, my first thought is to do something like this in my search:

| lookup ctg_supplier_customer_tpcode_lookup.csv supplier_TPCode AS TPCode OUTPUT customer_TPCode

My issue is tying customer_TPCode in with the results to show that the transaction went out to supplier, customer,. or both. At an individual transaction level of results, my search looks like this:

(index=gentran sourcetype=oms_invoice EDI=1 earliest=-19d@d latest=-12d@d eDiversity=Y)
OR
(index=contract_gateway sourcetype=esb_audit bp_bp_name=Invoice*)
| lookup ctg_supplier_customer_tpcode_lookup.csv supplier_TPCode AS TPCode OUTPUT customer_TPCode,processing_type,split_flag,format
| eval SourceSystem=case(isnotnull(SourceSystem), SourceSystem, sourcetype=="oms_invoice","OMS" )
| eval Amount=coalesce(Amount, InvoiceAmount)
| eval endpoint_name=coalesce(Endpoint,svc_context_name)
| eval transaction_format=coalesce(TransactionFormat,transaction_format)

| rex field=msg_stuff "[^|]*\|(?<transaction_format>[^|]*)\|.*"

| stats count as event_count,
    earliest(_time) AS start_time,
    latest(_time) as end_time,
    latest(bp_bp_name) as bp_bp_name,
    latest(Amount) as Amount,
    latest(TN_RECEIVERID) as customer_name
    values(TPCode) as TPCode,
    values(transaction_format) as transaction_format,
    latest(MasterAccountNumber) as MasterAccountNumber,
    latest(endpoint_name) as endpoint_name,
    latest(status) as exit_status,
    latest(other) as exit_message,
    values(SourceSystem) as SourceSystemValues,
    latest(bp_context_id) as bp_context_id,
by InvoiceNumber
| where like(SourceSystemValues, "%OMS%")

From here I rollup the transactions to get a high level count of total invoices sent/not sent for these particular customers:

| eval duration=tostring( (end_time - start_time) , "duration" )
| eval exit_status=upper(exit_status) 
| lookup temp_ctg_status_rollup_lookup.csv ctg_status AS exit_status OUTPUT ctg_rollup_status

| eval failure_dollars=if(ctg_rollup_status=="FAILED", Amount, 0)
| eval success_dollars=if(ctg_rollup_status=="SUCCESS", Amount, 0)
| eval processing_dollars=if(ctg_rollup_status=="PROCESSING", Amount, 0)

| stats count as total_invoice_count, 
sum(Amount) as sum_total, 
sum(failure_dollars) as sum_failure_rollup_dollars, 
count(eval(ctg_rollup_status=="FAILED")) as not_sent_count, 
sum(success_dollars) as sum_success_rollup_dollars, 
count(eval(ctg_rollup_status=="SUCCESS")) as sent_count,
count(eval(ctg_rollup_status=="PROCESSING")) as should_have_been_processed_by_now, 
sum(processing_dollars) as sum_processing_rollup_dollars,    
by TPCode
| table TPCode, total_invoice_count,sum_total,sum_failure_rollup_dollars,not_sent_count,sum_success_rollup_dollars,sent_count,should_have_been_processed_by_now,sum_processing_rollup_dollars

How can I expand this search to add the customer_TPCode to determine for a particular day how many transactions went out to either supplier_tpcode, customer_TPCode, or both? If it helps I also have a split_type column in the lookup that tells us whether these customers are configured for receipts to go out to supplier, customer, or both, but by grouping by InvoiceNumber we should be able to tell from the results.

Would it be best to just add all of the TPCodes in one column and search only on those? Any insight is greatly appreciated.

Thank you!

[Richfez]

Hi, KolGr001.

That's a lot of stuff to sift through. I have a possible way to do this, and one recommendation if it doesn't work.

You could try replacing all the parts of the search where there TPCode with the two individual ones, customer_TPCode and supplier_TPCode. So for instance in the first stats command, instead of

values(TPCode) as TPCode,

You'll have

values(customer_TPCode) as customer_TPCode,
values(supplier_TPCode) as supplier_TPCode,

Then use those.

A more general thought and recommendation in these situations would be to strip this down to its bare essence and recreate it one step at a time. Start with your root search and learn what your data looks like there. As you add each portion of the search back in, really try to understand what it changes - what gets added, what gets removed, how does it get summarized?

Do this as if you were trying to explain the process to someone of reasonable intelligence and education but with no Splunk knowledge and you will find this can often work wonders on ... well, a LOT of things. It can help you clean up messy searches or make things more efficient. You may find there's a far better, simpler way to turn your data into actionable knowledge. You might find that you are just trying to do too much in one search - that the single search would be far better replaced with 3 smaller, faster, easier to understand searches that each do one thing, and do it reliably, quickly, and in a very easy to understand way. Conversely you may also find that you are doing it perfectly, but now you'll know it's perfect instead of only guessing that it is.

Either way, enlightenment will follow from truly understanding your data from the bottom up.