topic Re: How do I calculate the Average time usage per day when using Transaction and Bin? in Splunk Search

How do I calculate the Average time usage per day when using Transaction and Bin?

maria2691 — Wed, 17 Jan 2018 15:36:11 GMT

Hello Everyone

I have 2 source types ProcessStart and ProcessEnd.
The common field with which I need to find out the duration of runtime is RunID.
My requirement is to find out the total time the processes are running in a particular host and show their average usage time per day.
Below is my Query:

 sourcetype=ProcessStart OR sourcetype=ProcessEnd 
 | transaction RunID 
 | bin _time span=1d 
 | stats sum(duration) AS Duration by _time host 
 | stats avg(Duration) as "Average Usage" by host 
 | eval "Average Usage"=round(('Average Usage'/3600),2) 
 | rename host as "Virtual Machine" 
 | sort- "Average Usage"

I have used Transaction on RunID and used bin command to segregate them day wise. I am able to get the final result by not by day wise. For eg: It should be below 24 hours even if I am checking the events for 100 days since the output is required to showing the total day wise average.

How do I make the final Average usage by host field to calculate the whole duration and divide them by number of days?

I used days=round(now()-_time) and tried to divide Average Usage/days and it doesn't work.

Also I have a performance issue when running this Query, it is very slow. What would be the reason and how can I rectify it?

Thanks & Regards
Maria Arokiaraj

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

somesoni2 — Wed, 17 Jan 2018 16:08:07 GMT

Do all those runID run everyday??

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

maria2691 — Wed, 17 Jan 2018 16:19:46 GMT

Hello @somesoni2

RunIDs are created every time there is a new task on the host. The ones which already exist, does not get updated.

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

somesoni2 — Wed, 17 Jan 2018 16:27:44 GMT

I believe you should be using stats command instead of transaction command (very expensive command) to mitigate your performance issue.

sourcetype=ProcessStart OR sourcetype=ProcessEnd 
  | eval start=if(sourcetype="ProcessStart",_time,null())
  | eval end=if(sourcetype="ProcessEnd",_time,null())
  | stats values(start) as _time values(end) as end by host RunID
  | eval duration=end-_time | bucket span=1d _time
  | stats avg(duration) AS Duration by _time host 
  | stats avg(Duration) as "Average Usage" by host 
  | eval "Average Usage"=round(('Average Usage'/3600),2) 
  | rename host as "Virtual Machine" 
  | sort- "Average Usage"

Also try this

sourcetype=ProcessStart OR sourcetype=ProcessEnd 
  | eval start=if(sourcetype="ProcessStart",_time,null())
  | eval end=if(sourcetype="ProcessEnd",_time,null())
  | stats values(start) as _time values(end) as end by host RunID
  | eval duration=end-_time | bucket span=1d _time
  | stats sum(duration) AS Duration by _time host 
  | stats sum(Duration) as "Average Usage" dc(_time) as days by host 
  | eval "Average Usage"=round(('Average Usage'/days/3600),2) 
  | rename host as "Virtual Machine" 
  | sort- "Average Usage"

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

maria2691 — Wed, 17 Jan 2018 16:44:38 GMT

Hello @somesoni2

I am able to get the result very quickly with these Queries, Thanks.
I would like to understand how the duration is calculated here.
When I used Transaction, I was able to get the duration by it's total running time (calculated between 2 events).
There are 2 fields RobotStart and RobotEnd in these events, if we use that, it would be perfect I suppose. They have time values that looks like "01/17/2018 16:42:07". Can we use these to get the exact results?

Thanks

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

somesoni2 — Wed, 17 Jan 2018 16:59:14 GMT

With above queries, I'm using _time field of the events from sourcetype=ProcessStart and sourcetype=ProcessEnd to calculate the difference (The stats on line 4 gets both epoch timestamp values for each RunID and line 5 calculates it). If you want to use timestamp values other that _time, then you need to update line 2 and 3 to use those fields instead of _time. E.g. (only showing line 2 and 3)

| eval start=if(sourcetype="ProcessStart",strptime(RobotStart,"%m/%d/%Y %H:%M:%S"),null())
   | eval end=if(sourcetype="ProcessEnd",strptime(RobotEnd,"%m/%d/%Y %H:%M:%S"),null())

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

maria2691 — Wed, 17 Jan 2018 17:38:25 GMT

Thanks a lot @somesoni2... It works with both the ones.

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

maria2691 — Tue, 06 Feb 2018 16:10:14 GMT

Hello @somesoni2

I have a problem with days calculation in the above solution shared by you.
The number of days return for some hosts so dramatically high. When I am checking the events for last 1 year, the max number of days should be 365 since we are calculating the dc(_time), however the results are like 18000 for some. How would it be possible, I am trying in different ways to correct it without any success. Can you help?

Thanks
Maria Arokiaraj

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

somesoni2 — Tue, 06 Feb 2018 16:28:51 GMT

In above query, there will be a row for each runId with different start time. What's the query you're trying?

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

maria2691 — Tue, 29 Sep 2020 17:57:59 GMT

I am trying with the same query, however the issue persists @somesoni2 😞
sourcetype=ProcessStart OR sourcetype=ProcessEnd
| eval start=if(sourcetype="ProcessStart",_time,null())
| eval end=if(sourcetype="ProcessEnd",_time,null())
| stats values(start) as _time values(end) as end by host RunID
| eval duration=end-_time | bucket span=1d _time
| stats sum(duration) AS Duration by _time host
| stats sum(Duration) as "Average Usage" dc(_time) as days by host
| eval "Average Usage"=round(('Average Usage'/days/3600),2)
| rename host as "Virtual Machine"
| sort- "Average Usage"

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

somesoni2 — Tue, 06 Feb 2018 17:04:18 GMT

So you're saying if you run following, you get ~18000 for value in column days?

sourcetype=ProcessStart OR sourcetype=ProcessEnd 
   | eval start=if(sourcetype="ProcessStart",_time,null())
   | eval end=if(sourcetype="ProcessEnd",_time,null())
   | stats values(start) as _time values(end) as end by host RunID
   | eval duration=end-_time | bucket span=1d _time
   | stats sum(duration) AS Duration by _time host 
   | stats sum(Duration) as "Average Usage" dc(_time) as days by host

In line 4, try to replace the values( function with max( for both start and end.

Re: How do I calculate the Average time usage per day when using Transaction and Bin?

maria2691 — Tue, 06 Feb 2018 17:17:59 GMT

Thanks @somesoni2. This worked 🙂 Now all the lines are around 200 days as expected.
Thanks again for your help!