topic Re: Stats and Look up in Splunk Search

Stats and Look up

Satyapv — Tue, 07 Nov 2023 04:52:27 GMT

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

Re: Stats and Look up

FelixLeh — Tue, 07 Nov 2023 09:39:55 GMT

| 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!

Re: Stats and Look up

Satyapv — Tue, 07 Nov 2023 10:23:38 GMT

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

Re: Stats and Look up

FelixLeh — Tue, 07 Nov 2023 10:38:07 GMT

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.

Re: Stats and Look up

Satyapv — Tue, 07 Nov 2023 10:53:40 GMT

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

Re: Stats and Look up

FelixLeh — Tue, 07 Nov 2023 11:46:26 GMT

| 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.

Re: Stats and Look up

Satyapv — Tue, 07 Nov 2023 11:51:45 GMT

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

CDEVersion1

CDEVersion2

Re: Stats and Look up

FelixLeh — Tue, 07 Nov 2023 12:11:24 GMT

| 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.

Re: Stats and Look up

Satyapv — Wed, 08 Nov 2023 07:36:23 GMT

Thank you it worked.