Eval time between events for transaction by group?

[gljiva]

Hi,
I'd like to do a report that tells me how long a forwarder hasn't been active. I use transaction to join similar events and next i would like to group events by host end eval time distance. Im having problem figuring out how to eval distance between same host (group by sourceHost) transaction events and show that as result.
Currently I use this search to get active forwarder connections:

index=_internal "group=tcpin_connections" startdaysago=1 | transaction sourceHost maxpause=2m maxevents=-1

This returns transactions for all uninterrupted connections, but i don't know how to calculate distance between events based od sourceHost and get information on how long a forwarder wasn't sending data.
All searches on http://www.splunk.com/wiki/Deploy:HowToFindLostForwarders show how to get current information about non-active forwarders and not report for all forwarders in a time period.

thx

[ftk]

I use the following search to find forwarders that have not checked in for a while (in this case more than 3600 seconds, or one hour):

| metadata type=hosts index=foo | eval last_contact=now()-recentTime | where last_contact>3600

If you drop the where last_contact>3600 you will get statistics for all your forwarders.

[ftk]

Well, the question was based around forwarders not all hosts including syslog, hence I contained the scope of my answer to forwarders only.

[Paolo_Prigione]

I think that approach would not work in case: a) your forwarders collect data from remote hosts too, b) your indexer receives snmp or syslog data from the network. In both cases your list of hosts would be much longer that those with a forwarder installed.

[Paolo_Prigione · Accepted solution]

Hi, if I understood you right, you want to report on the time lapse intercuring between "transactions" coming from the same host. With respect to this, I'd follow this approach:

1. create the transaction you are interested into; compute the time at which that transaction ended as *_time + duration*
2. invert the time line, so that later events come after earlier events
3. use streamstats to bring the previous' transaction end_time into current event, while taking care that only the last transaction from the same host is used
4. compute the time gap

That would translate in something like:

<some searh> | transaction host maxspan=10m maxpause=1m maxevents=10 
| eval end_time = _time + duration 
| sort + _time 
| streamstats avg(end_time) as prevendtime window=1 current=f global=false by host
| eval timegapsecs=round(_time - prevendtime,0)

timegapssecs will be the amount of time (in seconds) passed between two consecutive transactions from the same host

[gljiva]

Thx again 🙂 this is final search that I use as a dashboard: index=_internal "group=tcpin_connections" startdaysago=2 | transaction sourceHost maxpause=2m maxevents=-1 | eval end_time = _time + duration | sort + _time | streamstats sum(end_time) as prevendtime window=1 current=f global=false by sourceHost | eval ForwarderOfflineTime=round(_time - prevendtime,0) | where ForwarderOfflineTime NOT NULL | fields + sourceHost _time ForwarderOfflineTime | rename _time as Time | convert timeformat="%H:%M:%S-%d.%m.%Y." ctime(Time)