How to use the map command to add the total event times between created beginning and end times?

[ichesla1111]

I want to use the map command to add the total event times for each day during the time interval from 6am-6pm.

For each day....
the "earliest" token in my map command = start of each day+6hours (Start1)
the "latest" token in my map command = start of each day+18 hours(End 1)

Using the tokens I use the map command to search over my set Splunk search timeframe. In my map command...

   1. For each day, I subtract each events  Endtime from its starttime = Diff
   2. To get the total event time for each day, I sum the time differences (sum(diff)) to get  the "total_time_of_events"
   3. Next I take the info_max_time - info_min_time for each search (for each earliest and latest token searches) to get the time value for each 12 hour day.

4.  Finally I divide the total_event_time by the (search_time_span*100) for each search to get the total time percentage of events being pulled into Splunk by day

YET it is not working!! My search returns "No results found". May I please have help? What am I doing wrong?



CODE:

|table BLANK hour date_mday date_month date_year
|bin span=1d _time
|eval Month=case(date_month="august","8")
|eval Start=Month+"/"+date_mday+"/"+date_year
|eval start= strptime(Start,"%m/%d/%y")
|eval Start1=start+21600
|eval End1=start+64800

|map search="search (index...) earliest=$Start1$ latest=$End1$ |bin span=1d _time|dedup _time
|eval timeend=strptime(DateEnd,\"%m/%d%Y %I:%M:%S %p\")
|eval timestart=strptime(DateStart,\"%m/%d/%Y %I:%M:%S %p\")
|eval diff=round(timeend-timestart)|stats sum(diff) as total_time_of_events by BLANK
|addinfo |eval IntTime= info_max_time-info_min_time
|eval prcntUsed=round((total_time_of_events/(IntTime))*100)
|rename prcntUsed as Percent_of_event_time"

[maciep · Accepted solution]

generally speaking, when your search doesn't have any results, then a good approach is start at the end and remove lines until you have results.  In your case, maybe ensure the "top" search works the way you want.  And make sure the mapped search works the way you want (w/o the map, just the search,including earliest/latest formatted the same way as the vars).  And if either don't work, troubleshoot them.  If they both work, then try making the top search create a single row for testing and then troubleshoot the mapped search from there.  And then add additional rows in the top search once the mapped search works. That would be my approach at least.  

that said, do you need map for this?  looks like you're just gathering stats per day and doing some calcuations from there?  seems like you could incorporate the logic from the top search into the main search the map is using?

index=whatevs
| eval start=relative_time(_time,"@d+6h"), end=relative_time(_time,"@d+18h"), day=strftime(_time,"%D %T")
| where _time >= start AND _time <= end
| eval timeend=strptime(DateEnd,"%m/%d%Y %I:%M:%S %p")
| eval timestart=strptime(DateStart,"%m/%d/%Y %I:%M:%S %p")
| eval event_time = round(timeend - timestart)
| stat sum(event_time) as event_time by day

[ichesla1111]

Thank you!!! It worked! A lot more efficient then mine hahaha.