Can you help me with a report and a scheduled alert?

[saibal6]

I have a set of table formatted data where the data is related to Date, specific store and the total count of customers that are coming to that store on an everyday basis. Now, I want to create a report where I can scheduled an alert only for those stores which have no customers visit on yesterday.

Is this possible?

I don't know how can I be able to do that, and I didn't find any proper command to make this type of report.

Please suggest me the best command and give a small example also.

[woodcock]

First, get a list of all possible stores. This is the Sentinel Search problem discussed (with solution) here:
https://conf.splunk.com/session/2015/conf2015-LookupTalk.pdf

[renjith_nair]

@saibal6,

Yes it's possible. One of the easiest method is to create a lookup file with the store information which can be used as a master data.
So even if the store is missing from the events for few days, you could still compare.

Assuming that we have a lookup file stores.csv which contains a list of stores

|inputlookup  stores.csv|eval isFound=0|append [index="your store index" earliest=-d@d latest=@d
|stats count by store|eval isFound=1] | stats count,max(isFound) as isFound by store|where isFound=0

[saibal6]

Thanks @renjith.nair for your quickly reply.

Let me use it as you said. I'll let you know if I'm having any quires or if it's working properly as I wanted. i'll accept the answer. 🙂 😄

Thanks in advance @saibal6 🙂

[saibal6]

Hi @renjith.nair,

I have successfully created a lookup file and it's successfully working but your above mentioned search command didn't give me any data or any kind of error whether I have an issue today and it's still showing me "no results found".

could you please help me on that?

[renjith_nair]

@saibal6,
Are you getting result if you remove the condition |where isFound=0
Does the lookup give you all store names in |inputlookup stores.csv ?
Whats is the heading/field name in the look file ? Is it same as in events? (in the example, I used store)

[saibal6]

Yes, Lookup gave me all the stores name in my lookup file "Portal.csv"
My filed name in the lookup is StoreNumber.

Before removing |where isFound=0 I didn't get any data but after removing this I got some events but not statistics and all those events are wrong.

[saibal6]

is it possible to use "fillnull value" instead of "isFound?"

[renjith_nair]

isFound is just used as a flag to determine from where the data is coming. When you add where isFound=0 , it only displays the stores which is not part of the events.
Do you have the StoreNumber in your events as well ?

[saibal6]

Hi @renjith.nair ,

Yes, I have the StoreNumber in my events as well.

I have already solved my issue with the help of other command and it's working successfully. Thanks for giving me the lookup table idea to do it. 🙂

Regards,
@saibal6