How to display valid values in lookup against each calendar date?

[hwakonwalk]

Hi,
I have a requirement where I need to calculate location wise weekly, monthly and total expected revenue for the devices installed. The data is in an Excel lookup as:

Device_Id            Location_Id          Start_Date          End_Date
60000099             20                   8/5/2017            10/5/2017
60000091             20                   5/5/2017            10/5/2017
60000092             40                   8/5/2017             
60000093             50                   7/5/2017            11/5/2017

In the expected result, I want the data for the time duration to be displayed as:

Date                     Location                 Number_of_Valid_Kits     
5/5/17                   20                               1
6/5/17                   20                               1
7/5/17                   20                               1
7/5/17                   30                               1
8/5/17                   20                               2
8/5/17                   30                               2   
8/5/17                   40                               2                   
and so on ...

I can evaluate the validity of kit based on start and end dates but I have no idea about how I can display all the data against each date so that I can search the data from the above format using dates and group it by the location_id

Thanks,
Zubair

[somesoni2]

Try like this

your current search giving fields Device_Id, Location_Id, Start_Date, End_Date
| eval Date=mvrange(strptime(Start_Date,"%d/%m/%Y"),strptime(End_Date,"%d/%m/%Y")+86400,86400)
| mvexpand Date| eval Date=strftime(Date,"%d/%m/%Y")
| table Date, Device_Id, Location_Id

You now go a row for each date from Start_Date to End_Date, including End_Date and you can do your evaluations.

[hunters_splunk]

Dear Zubair,

I think you can just aggregate your devices by start date and location:

 <query to return all valid events> ... | stats count(Device_Id) by Start_Date, Location_Id

Hope it helps. Thanks!
Hunter

[hwakonwalk]

Hi Hunters, The Start date won't do as there may be many days when no kit is installed but still many kits stand valid on that day. I missed to keep an empty date in the example but in real scenario, the many kits are deployed once and then few more are added and if any kit has a problem, it is removed