We have devices which maintains session information of various users. These devices have a max capacity of sessions. The device pumps logs as follows:
Time User SessionCount Device 10 AM A 3 D1 10 AM B 4 D1 11 AM A 10 D1 11:30 AM A 3 D1
Our summarization happens once in an hour. During summarization the Capacity value is also added to maintain the history. Our Summarization index looks like
Time User SessionCount Device Capacity 10 AM A 3 D1 30 10 AM B 4 D1 30 11 AM A 13 D1 30
Now, i need to display a chart over time with SessionCount and Available Capacity(Capacity - SessionCount).
We have a device pulldown with default value as All. In this case in search query Device="*" will be there. If Device is chosen then it will be, Say Device="D1"
For me to display the chart properly, the Capacity should be summed at unique devices alone. It should not be summed up across events then the capacity will be summed up for same device.
How will i write search, so that i get the resultset as:
Time SessionCount Available 10AM 7 23 11AM 13 17
However i try, i am not getting the desired output.
You can probably save some compute cycles with a different search. Let's assume that you have the Device and Time fields available as part of your summary.
index="summary" sourcetype="my_summary_data" | stats sum(SessionCount) AS SessionCount values(Capacity) AS Capacity by Device Time | eval Available=Capacity-SessionCount
That will likely provide you a result like this:
I hope this helps.
You had the right idea, but you can do a few things to simplify / make your search more efficient:
1) you don't need a bucket and stats separately. You can simply use a timechart command
2) you can perform both calculations in the same stats or timechart command instead of an appendcols
3) I've added to the timechart to group by device
It should look something like this:
searchPart1 | timechart span=1h sum(SessionCount) as ActiveSessions max(Capacity) as MaxCapacity by device | eval Available = MaxCapacity - ActiveSessions | fields _time device ActiveSessions MaxCapacity
Hope this helps
I did this
searchPart1 | bucket _time span=1h | stats sum(SessionCount) as TotalSession by _time | appendcols [searchpart1 | bucket _time span=1h | dedup Device _time | stats sum(Capacity) as Available by _time] | eval AvCap = (Available - TotalSession) |fields TotalSession AvCap
Is there better way to achieve than what i have done