topic Re: Question on Search in Splunk Search

Question on Search

strive — Tue, 28 May 2013 08:56:19 GMT

Hi,

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.

Thanks

Strive

Re: Question on Search

strive — Tue, 28 May 2013 09:33:42 GMT

I did this

Is there better way to achieve than what i have done

Re: Question on Search

aholzer — Tue, 28 May 2013 20:37:02 GMT

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

Re: Question on Search

Gilberto_Castil — Tue, 28 May 2013 20:40:14 GMT

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.