topic Re: How to edit my search to get total time of events (last-first) and sum by source, by host? in Getting Data In

How to edit my search to get total time of events (last-first) and sum by source, by host?

smhsplunk — Mon, 10 Oct 2016 19:06:00 GMT

So I am trying to get the cumulative sum of all the time taken by each host, so far I could cumulate for a single host, how can i loop through all the hosts and show it in a table

index=main host="XYZ"  
                | stats earliest(_time) as First latest(_time) as Last by source 
                | eval difference=Last-First 
                | stats sum(difference) as total_difference 
                | eval todifference=tostring(total_difference, "duration")  
                | table todifference

host1, time-taken
host2, time-taken

And then perhaps plot the time in a timechart with x-axis with host-names and Y-axis with time taken

Re: How to edit my search to get total time of events (last-first) and sum by source, by host?

sundareshr — Mon, 10 Oct 2016 19:25:38 GMT

How about this? Do you need the first and last by source? or by host?

 index=main   
                 | stats earliest(_time) as First latest(_time) as Last by host 
                 | eval difference=Last-First 
                 | eval todifference=tostring(total_difference, "duration")  
                 | table todifference

Re: How to edit my search to get total time of events (last-first) and sum by source, by host?

somesoni2 — Mon, 10 Oct 2016 19:36:41 GMT

Try like this

index=main host="*"  
                 | stats earliest(_time) as First latest(_time) as Last by host source 
                 | eval difference=Last-First 
                 | stats sum(difference) as total_difference by host
                 | eval todifference=tostring(total_difference, "duration")  
                 | table host todifference

If you convert your duration to string, you would not be able to plot it. (y-axis values should be numeric) So remove the | eval todifferen... from the above search and use the appropriate visualization.

Re: How to edit my search to get total time of events (last-first) and sum by source, by host?

smhsplunk — Mon, 10 Oct 2016 19:37:48 GMT

It has to go through all the sources to find the total time taken by each source, then add those times

What the above does is that it gives you time difference between the last event of last source minus first event of first source (you can have many times in between where no events may not happen...), hence by source in my code.

so first and last by source and then add it cumulatively to find by host
Then show table exact time taken by each host

I wanted to do "by source by host" but it doesnt work

Re: How to edit my search to get total time of events (last-first) and sum by source, by host?

smhsplunk — Mon, 10 Oct 2016 19:43:11 GMT

This seems to give me all times over all the hosts. I want total time by each host

host1 time-takenX
host2 time-takenY

Re: How to edit my search to get total time of events (last-first) and sum by source, by host?

somesoni2 — Mon, 10 Oct 2016 19:48:15 GMT

Strange, if you're using the query as-is, it's should give you one row per host as we're using | stats sum(difference) as total_difference by host . Can you double check?

Re: How to edit my search to get total time of events (last-first) and sum by source, by host?

smhsplunk — Mon, 10 Oct 2016 19:54:15 GMT

You are right! Sorry I missed that part,
I will get started with the plotting now