Getting Data In

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

smhsplunk
Communicator

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

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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.

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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.

smhsplunk
Communicator

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

host1 time-takenX
host2 time-takenY

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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?

0 Karma

smhsplunk
Communicator

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

0 Karma

sundareshr
Legend

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

smhsplunk
Communicator

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

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 ...