How to display country and its top store?

[niks987]

Hello,

I'm trying to display country's name along with the top store name of the country. I have used a lookup file which stores country name and store name. I tried using top command but it is not displaying top store name.

| inputlookup store_name.csv | lookup store_name.csv Storenum as store_num OUTPUT StoreName city state | top 1 StoreName by state

I also tried using a subsearch but m unable to get any results.

[FrankVl · Accepted solution]

Since you mention the order information is in sourcetype="store_details"

A search could look something like this (probably best to also add an index="..." criteria to the initial search, but I left that out since you did not specify which index you use):

sourcetype="store_details"
| lookup store_name.csv Storenum as store_num OUTPUT StoreName city state
| top 1 StoreName by state

If that does not give the desired results, you'll need to provide more info on what the data in that sourcetype looks like and what results you get and what is wrong with that (if any results at all).

[niks987]

HI FrankVI,

I tried using your query but it displays only 2 country and there top store. I have tried earlier but without inputlookup few of the country's will be displaying. Please find snippet of my lookup file below:-

Storenum StoreName city state/country
0 abc xyz0
1 xyz xyz1 UK
2 pqr xyz2 UK
3 123 xyz4 USA
4 456 xyz5 USA
5 789 xyz6 India

And sample of sourcetype data:-
1234-12042019172103_CSE.xml4703204768501.gz

First 4 digit is the store number.

[splunk_sv]

Could you please add a sample entry of your lookup file.

[niks987]

I tried to using query provided by you but it only displays 2 country and its top store. If i don't use inputlookup rest of the country's wont be displayed.

Storenum StoreName city state/country
0 abc xyz0

1 xyz xyz1 UK
2 pqr xyz2 UK
3 123 xyz4 USA
4 456 xyz5 USA
5 789 xyz6 India

This is the sample entry of the lookup file.
For top 10 states i had to use inputlookup because if i use lookup it wont display all the countries/states.

[somesoni2]

What is the criteria to decide which store is the "top" store for the country? Generally top command works based on count of events. But since your lookup might have single entry for a country-store combination, you migth not get correct result.

[niks987]

The criteria to decide which store is the top store is by the number of orders coming to the store. Yes in my lookup file there is single entry for all the country-stores combination.

[FrankVl]

So where is the information on that number of orders then?

[niks987]

The information about number of orders are in sourcetype="store_details"