Stats and Look up

[Satyapv]

Dear All,

I have look up file with Transaction details and Transaction Name Like below. Will be great if someone suggest hot to handle below scenario. 

Tran_lookup    Transaction_Details

ABC     Shopping

CDE    Rent

From my splunk index i am running Stats command like below (Tran from index = Tran_lookup) from 

count(Tran) as count , Avg( responstime) as avgrt by Tran 

I need to add matching Transaction_Details from lookup  to the final stats results:

Current Results:

Tran   Count avgrt

Required Results (Matching Transaction_Details  to be pulled based on Tran ) 

Tran Transaction_Details  Count avgrt

[Satyapv]

Thank you it worked.

[FelixLeh]

| lookup <lookup_name> Tran_lookup as Tran OUTPUT Transaction_Details

Its recommended to create a lookup definition for your lookup files.
If you do so you replace the lookup name with the name of the lookup definition.

Hope this helps!

[Satyapv]

hello,

how can we combine stats and lookup on comparison of Tran and Look_Tran.

Current Results:

Tran   Count avgrt

Required Results (Matching Transaction_Details  to be pulled based on Tran  from lookup)

Tran Transaction_Details  Count avgrt

[FelixLeh]

With the lookup command I mentioned in my first reply you can do so.
You have to replace "<lookup_name>" with the name of your lookup. If the field names you mentioned are correct then the query should work. Just add the line after the current result.
If it still doesnt work pls provide more information like the name of the lookup file, the exact names of the fields in the result and the exact names of the fields in the lookup file.

[Satyapv]

Look up file name is TransctionDetails.csv

if look at my initial post I have another query of stats to which I need to append

stats count(Tran) as count , Avg( responstime) as avgrt by Tran where Tran_lookup matches with Tran in index.

Expected output is ( Corresponding Transaction Details should be added to stats output).

Count ,Tran, Transaction_Details, avgrt 

[FelixLeh]

| stats count(Tran) as count , Avg( responstime) as avgrt by Tran
| lookup TransctionDetails.csv Tran_lookup as Tran OUTPUT Transaction_Details
| fields Count ,Tran, Transaction_Details, avgrt 

if you only want the Events that have matching Transaction_Details add:

| where isnotnull(Transaction_Details)

My Expectation in the Example is that the Lookup is inside of Splunk and correctly configured.

[Satyapv]

Thank you. It worked.

However i was reviewing index data Splunk index has data like below with versions of Tran. But lookup file only has ABC and CDE as Transaction_Details is same for all versions of Tran. Such cases where there are multiple versions of Tran and 1 value of Transaction_Details is coming is empty. Is there a way lookup file can be searched for Prefix or some other way?

Tran

ABC

ABCVersion1

ABCVersion2

ABCVersion3

CDE    

CDE

CDEVersion1

CDEVersion2

[FelixLeh · Accepted solution]

| stats count(Tran) as count , Avg( responstime) as avgrt by Tran
| rex field=Tran "(?<temp>^[A-Z]{3})"
| lookup TransctionDetails.csv Tran_lookup as temp OUTPUT Transaction_Details
| fields Count ,Tran, Transaction_Details, avgrt 

I extract the first three letters from the Tran field and put it in a separate field. Then I make the lookup against the file with the new field.