Splunk Search

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

maria2691
Path Finder

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

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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"

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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"

maria2691
Path Finder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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())
0 Karma

maria2691
Path Finder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

maria2691
Path Finder

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"

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

maria2691
Path Finder

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

0 Karma

maria2691
Path Finder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Do all those runID run everyday??

0 Karma

maria2691
Path Finder

Hello @somesoni2

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...