How to create a correlation using Splunk data and a CSV

[_gkollias]

Hello,

I'd like to create a search to show how many transactions are in Splunk compared to how many orders are on the CSV (the number of orders should be the same, but right now we have no visibility to what orders actually hit what is it in the CSV). The goal here is to discover how many transactions are not on the CSV.

I stored the CSV in my home directory, and Splunk picks up the data and forwards it to the following:

                 index=contract_gateway sourcetype=contract_sunrise

All other orders are located in the same index

                 index=contract_gateway

Is there a way to display a correlation to table the total amount of orders that are in Splunk compared to how many are in the CSV?

Your help is greatly appreciated.

Thanks in advance!

[chris]

This might work:

index=contract_gateway | rex  "OrderNo:\s(?<OrderNumber>[^,]+),"| rename psrsvd_ss_memUsedMB as OrderNumber  | stats  values(sourcetype) as sourcetypes by OrderNumber | where isnull(mvfind(sourcetypes, "contract_sunrise")

This should give you all the OrderNumbers that appear in the index contract_gateway but not in csv sourcetype "contract_sunrise"
- the stats command will merge together all the different occurences of an order in different sourcetypes to one resulting event
- the where clause will filter out all the events that occur in your contract_gateway index but are not in the csv ( which is the contract_sunrise sourcetype)

You might be better off configuring your csv as a lookup and then search for events that don't match:
http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Lookup

[_gkollias]

This actually gives me millions of events back since it's looking at all of the other sourcetypes except contract_sunrise. Is there a way correlate the OrderNumber count by making a match? IE for 09/16, this many orders made it to our OMS (the list from the CSV via sourcetype=contract_sunrise) compared to this many orders we see in Splunk that are not on the the list? Please let me know your thoughts!

[chris]

I updated the answer

[_gkollias]

Great idea on using a lookup! I assume there will be a modified search for that. So I could do something like ...base search | lookup as400_orders_lookup.csv OUTPUT OrderNumber|...?

[chris]

Thinking about it you don't even need a lookup a csv is enough. Place it in /var/run/splunk. The csv should contain a column with the title OrderNumber

index=contract_gateway | rex  "OrderNo:\s(?<OrderNumber>[^,]+),"| rename psrsvd_ss_memUsedMB as OrderNumber  |search  NOT [ inputcsv as400_orders_lookup.csv | fields OrderNumber ]

Take a look at the job inspector it will show you what the search expands to. Especially the subsearch in the [ ]

[_gkollias]

OK cool! I added the csv. I think my problem lies in that since orders are routed different, they are stored in different indexes as well. So I think I need to do some more digging and find which indexes they lie in and do an append at first...then maybe switch to index=* (instead of just the initial OrderNo: which you helped extract for a single order). To start, is there a way to table the match just from
orders in index=contract_gateway sourcetype=esb_audit and the OMS csv (index=contract_gateway sourcetype=contract_sunrise)? Your help is greatly appreciated!!

[chris]

I do not know what your data for the esb_audit looks like. But assuming there is an OrderNumber field (if esb_audit does not have an OrderNumber field you have to create it with rex):

index=contract_gateway sourcetype=esb_audit  NOT [search index=contract_gateway sourcetype=contract_sunrise | fields OrderNumber]

index=contract_gateway (sourcetype=esb_audit OR sourcetype=contract_sunrise)| stats  values(sourcetype) as sourcetypes by OrderNumber | where isnull(mvfind(sourcetypes, "contract_sunrise")

[chris]

Do you have any sample events? Is there something the 2 types of events have in common? What sourcetype do the "other orders" have?

[_gkollias]

I tried doing something like index=* 12345 (OrderNumber) to show all of the different indexes the order went through, but I don't necessarily want to track one order from beginning to end. Instead, what I want to try and do is say ok - using this CSV, here are all of the orders in splunk compared to how many are in the CSV. This helps us to find visibilty from CTG to our order mgt system on how many/ which orders made it to the OMS, and which did not. Maybe I need to compare a count? I'm not quite sure how to go about it., but it would great to find some visibility here.

[chris]

If the OrderNumber is a field you I see a solution, if you can post some/one (fake) event/s we can help you create a field. The you can try something like the following search which will list you the orders that do not occur in the sourcetype created by the csv:

base search | stats dc(sourcetype) as type values(sourcetype) as val by OrderNumberField | search type<2  val!=contract_sunrise

[_gkollias]

Great!! The CSV has headers, and I added some configs in props.conf to extract the values and remove the headers..The field is OrderNumber here, and I created a dummy sourcetype (contract_sunrise). I took one order number and found it in two other sourcetypes. The events look like the following:

%Y-%m-%d %H:%M:%S,3Z OrderStatus- INFO OrderStatus - OrderStatus: run: success: Updated OrderNo: OrderNumber, ProcTime(ms): 91 DtlCount: 4, Emailed:

The next is in a summary search
OrderNumber here is found in this field - psrsvd_ss_memUsedMB=OrderNumber

Each order is also routed differently