How do I edit my search to append a subsearch result to a count on an hourly basis?

[qiaojing]

Hi,

I'm trying to create a scheduled report that runs daily at 3am. The use case is to track the occupancy number of a carpark on an hourly basis and output a CSV spreadsheet.

At 3am when the report runs, I will need a base count. For the hourly count subsequently, the result will be added to the base count ( e.g. at 3am, the base count is 100; at during 3am-4am 3 cars entered the carpark, hence the base count should be updated to 100+3=103). The output result daily will only show base count that is updated at every hour.

I came up with a search string, but it isn't giving me any results and I have no idea how to fix it.

sourcetype="bn22_epsin"  [search sourcetype="bn22_epsin" earliest=-25h latest=-24h |stats count as baseIN ] | bin span=1h _time | stats count as IN by _time | eval baseIN=if(IN!=0, IN+baseIN, baseIN)  | table baseIN IN

I'm trying to find the base count at 3am in the subsearch by counting events from 2-3am.

Please help me out. Any suggestions will be greatly appreciated. Thank you very much!

[sundareshr]

Not sure I understand your question. But to get the count from 2:00am to 3:00am, use this earliest=@d+2h latest=@d+3h. What this will give you is the number of events between 2 & 3. Not sure if that is necessarily the "base count". For incremental counts after 3:00am, you could do this earliest=@d+3h | timechart span=1h count. To do a comparison. Try this complete search

sourcetype=bn22_epsin earliest=@d+2h | eval hour=strftime(_time, "%H") | eventstats count(eval(hour=2)) as baseIN | where hour>2 | timechart count as IN max(baseIN) as baseIN | fields - _time 

However, if you baseIN is the count of all events in 24hrs prior to 3:00am (previus day 3:00am to Today 3:00am). Your search would look like this

index=* earliest=-1d@d+3h | eval group=if(_time>relative_time(now(), "@d+3h"), "Increment", "Base") | eventstats count(eval(group="Base")) as baseIN | where _time>relative_time(now(), "@d+3h") | timechart span=1h count max(baseIN) as baseIN cont=f

[qiaojing]

@sundareshr Hi thank you so much for your answer. I'm currently trying to get the base count from 2-3am so i'm following your first code.

I'm trying to accumulate all the counts from 3am, so the count at 4am consist of 2-3am + 3-4am ones. I'm doing this because the code above is only for cars going IN the carpark, I'll have another search for cars going OUT of the carpark. Hence in order to find the occupancy of a particular carpark at every hour, i'm planning to take the difference between IN and OUT. This is just my logic, I welcome any other ideas of how to find the carpark occupancy. 🙂

I tried out the first code and have a few questions:
- May I know what does max(baseIN) gives me?
- The code only output results until 8am, not really sure why...
- Just to clarify my own understanding, using timechart span=1h will give me the count from eg 3-4am right?

Thank you very much! 🙂

[sundareshr]

• May I know what does max(baseIN) gives me?
  baseIN in calculated in the the eventstats command. It has the count of events between 2 & 3 am. max(baseIN) just get the max of value of that field. But since we are doing eventstats, it will be the same value. You can use min, max, avg, values, first, last any of these, you'll get the same result.

• The code only output results until 8am, not really sure why...
  It should return from 2 am till the time you run (current time). So if you run it at 8 am, it will show events till 8 am.

• Just to clarify my own understanding, using timechart span=1h will give me the count from eg 3-4am right?
  List item timechart span=1h will give you count in hourly increments. So, in your example, it will be 3-4, 4-5, 5-6, 6-7 etc..

If you want to accumulate the counts, add this to your search

.... | accum IN as IN