topic Re: Urgent !! Complex case statements in eval in Splunk Search

Urgent !! Complex case statements in eval

sushmitha_mj — Thu, 28 May 2015 15:52:00 GMT

I have a field that captures the usage of every user. I want to write a case to get the usage of each user in that hour, which is the max(usage). But since this value is cumulative. I want to subtract the value of the previous hour. Foe example: In the timechart, for 2:00 the value will be sum( max(usage) by each user at 2:00) - sum(max(usage) by user at 1:00 ). This has to be dynamic, since this is a timechart it has to apply the same logic to each hour.
Plz help.

Re: Urgent !! Complex case statements in eval

aalanisr26 — Thu, 28 May 2015 16:15:12 GMT

can you provide 1 sample event?

Re: Urgent !! Complex case statements in eval

sushmitha_mj — Thu, 28 May 2015 16:46:32 GMT

@aalanisr26

Example:
User---- Usage--- Time---------- Status
A-----------10-----------1:00PM----- Start
A-----------15----------2:00 PM------In progress
A-----------25----------3:00 PM------In progress
A-----------30----------4:00 PM------Stop
B-----------15----------2:00 PM-------Start
B------------20----------3:00 PM-------In Progress
B------------25----------4:00 PM--------Stop

The usage for each hour is cumulative for each user.

So what I need is:
Hour ------Usage
1:00 ------10 (user A-10)
2:00------20 ( User A-5 + User B 15)
3:00----- 15 (User A- 10 + user B 5)
4:00----- 10 (User A-5 + user B-5)

Does that help you understand better?

Re: Urgent !! Complex case statements in eval

woodcock — Thu, 28 May 2015 17:01:09 GMT

I believe that you need the streamstats command; read about it and reclarify your needs if this won't work (or you are not sure how to make it work):

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/streamstats

Re: Urgent !! Complex case statements in eval

sushmitha_mj — Mon, 28 Sep 2020 20:05:39 GMT

@woodcook
I tried it, But I am unable to make it work. Could you give me an example of how to make this work?

If this is my query:
| pivotAccounting Accounting sum(Input) AS "Input" SPLITROW _time AS _time PERIOD hour LIMIT User_Name BY TOP 1 sum(Input) | eval usage=round(Input/1000,2)

Note: In this query "User_Name BY TOP 1 sum(Input) " is wrong. I want to actually get the max(sum(Input)) by user for this hour and subtract it from max(sum(Input)) for user of previous and finally add all this value to get overall usage. I do not know how to.
Plz..... help....

Re: Urgent !! Complex case statements in eval

aalanisr26 — Thu, 28 May 2015 19:10:12 GMT

Ok assuming the following dataset:

05/28/2015 1:00PM User=A Usage=10 Status=Start
05/28/2015 2:00PM User=A Usage=15 Status=In progress
05/28/2015 3:00PM User=A Usage=25 Status=In progress
05/28/2015 4:00PM User=A Usage=30 Status=Stop
05/28/2015 2:00PM User=B Usage=15 Status=Start
05/28/2015 3:00PM User=B Usage=20 Status=In Progress
05/28/2015 4:00PM User=B Usage=25 Status=Stop

try the following query:

source="sample.log"  sourcetype="sample" |stats sum(Usage) as Usage1 by _time |streamstats max(Usage1) as Usage2 current=f |eval TotalUsage=Usage1-coalesce(Usage2,0)

Re: Urgent !! Complex case statements in eval

woodcock — Thu, 28 May 2015 19:41:45 GMT

Forget the pivot. Assuming the field names in your sample data are correct and assuming that you are timestamping based on your Time field, this should work:

... | sort 0 + _time | streamstats usecurrent=f last(Usage) AS PrevUsage by User | eval UsageThisHour=usage - coalesce(PrevUsage,0) | stats sum(UsageThisHour) by Time

Re: Urgent !! Complex case statements in eval

woodcock — Thu, 28 May 2015 19:54:45 GMT

I do not think you meant max(Usage1), did you?

Re: Urgent !! Complex case statements in eval

aalanisr26 — Thu, 28 May 2015 20:01:07 GMT

yes I did, load the data and run the query

Re: Urgent !! Complex case statements in eval

acharlieh — Thu, 28 May 2015 20:02:02 GMT

Let's say that in the 6pm hour user A started using again, would her usage start again at 0, or would it start at 30?

Re: Urgent !! Complex case statements in eval

sushmitha_mj — Thu, 28 May 2015 20:17:56 GMT

@aalanisr26
First of all Thank you so much... This seems to kind of works. But 2 issues.

We are not grouping this by user so it sums all usage as usage 1 and 2nd max of this as usage 2. I tried adding user in the "by" section along with _time. For some reason did not work.
A new problem is that some users have a couple of start, in progress and stop sessions within the frame so this logic does not work.
For example

05/28/2015 1:00PM User=A Usage=10 Status=Start
05/28/2015 2:00PM User=A Usage=15 Status=In progress
05/28/2015 3:00PM User=A Usage=25 Status=In progress
05/28/2015 4:00PM User=A Usage=30 Status=Stop
05/28/2015 2:00PM User=B Usage=15 Status=Start
05/28/2015 3:01PM User=B Usage=20 Status=In Progress
05/28/2015 3:05PM User=B Usage=25 Status=In Progress
05/28/2015 3:20PM User=B Usage=30 Status=Stop
05/28/2015 3:25PM User=B Usage=10 Status=Start
05/28/2015 3:30PM User=B Usage=15 Status=In Progress
05/28/2015 4:00PM User=B Usage=25 Status=Stop

So in this case, For the 3:00 PM time range: it has to take the usage of B to be stop value 30 + latest "In progress" value 15

Could you plz help?

Re: Urgent !! Complex case statements in eval

woodcock — Thu, 28 May 2015 20:41:39 GMT

Did you try my solution (different from @aalanisr26)?

Re: Urgent !! Complex case statements in eval

tincupchalice — Thu, 28 May 2015 20:47:58 GMT

so you can't just

| bin _time span=1h
| stats max(Usage) as maxUser by _time User
| stats sum(maxUser) by _time

Re: Urgent !! Complex case statements in eval

sushmitha_mj — Mon, 28 Sep 2020 20:06:21 GMT

Thanks everyone. Finally this is the query I used

bucket _time span=1h | stats first(Record) as Current, max(Input) as inp by _time, User_Name | streamstats max(inp) as Usage2 by User_Name current=f | eval delta=if(isnull(Usage2),0,Usage2 ) | eval usage=round((inp-delta)/1073741824,2)