Building a Search for Comparison/Match

[_gkollias]

I have a search that brings up specific order types by order numbers that begin with a 7:

index=contract_gateway sourcetype=esb_audit earliest=-1d@d latest=@d esb_top_level=1 NOT bp_bp_name="Task"
svc_context_name="orderType.channel." OR
svc_context_name="orderType:receive" OR
svc_context_name="orderType:receive"
| stats earliest(_time) as _time count AS event_count, first(OrderNumber) AS OrderNumber first(svc_context_name), latest(status) AS exit_status, latest(GrandTotal) AS GrandTotal by svc_context_id
| search OrderNumber=7*

Basically this tables time, UUID(svc_context_id),eventcount, ordernumber, ordertype/servicename, exit_status(the status of the order as it exits - success,fail, etc.), and grand total ($).

What I am doing is trying to compare these orders with orders I receive in a .CSV from an AS400 to try and make a match/ find out what is not coming in Splunk that is in the CSV.

Since forwarding the CSV is currently a manual process, I have the inputs of the .CSV being forwarded/indexed through a different index and sourcetype:

index=contract_gateway sourcetype=contract_sunrise order_num="7*" - all of the order numbers I am concerned with are 10 digit numbers that begin with 7.

Is there a way I can combine the searches (i.e. via ...| append [ ) and be able to list/sort order numbers that begin with a 7 that are in the CSV but not in Splunk?

Thanks in advance!

[wpreston · Accepted solution]

You could try using a subsearch to find the orders that are in the .csv but not in Splunk. Combine the subsearch with a NOT and it will basically make a big list of order numbers that Splunk will exclude from the search results. Something like this...

index=contract_gateway sourcetype=contract_sunrise order_num="7*" NOT [search index=contract_gateway sourcetype=esb_audit earliest=-1d@d latest=@d esb_top_level=1 Order_Number=7* NOT bp_bp_name="Task" svc_context_name="orderType.channel." OR svc_context_name="orderType:receive" OR svc_context_name="orderType:receive"  | fields order_num | dedup order_num]

This will bring back all of the order numbers in your .csv file (beginning with a 7) that are NOT in Splunk.

[_gkollias]

thanks! I will probably add the table command to list the order numbers as well