Splunk Search

Question on Search

strive
Influencer

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

Tags (1)
0 Karma

Gilberto_Castil
Splunk Employee
Splunk Employee

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:

alt text

I hope this helps.

0 Karma

aholzer
Motivator

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

0 Karma

strive
Influencer

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

0 Karma