topic Re: Transaction - setting finish time to the start of next transaction in Splunk Search

Transaction - setting finish time to the start of next transaction

eli_mz — Tue, 29 Sep 2020 16:33:38 GMT

Is it possible to set the end time in a transaction to the start time of the next transaction? So instead of "end_time=_time+duration" I'd want "end_time=start_of_next_transaction" (if only that would work! 😄 ).

I'm trying to write a search (part of a larger search) that will give me the IP address of a host and the date\time that host had said IP address based on DNS logs. I've written the search below that gives me the start and "false" end time of when the host would have had an IP associated with it according to DNS. The assumption I'm making is that the IP will be tied to the host until a new host registers in DNS to that IP, but the transaction below give an end time of the last time the host refreshed in DNS (so the start of the last session).

Here's an example of the raw logs I'm working with.

2017-10-20T00:02:10.739976-05:00 198.172.82.22 dns-dnsupdated[12593]: Updating DNS information for MY_COMPUTER-192193176212.st.domain.com:192.193.176.212 which previously had the hostname MY_COMPUTER-1.st.domain.com.

Search string:

index="network" sourcetype="DNS" "Updating DNS information" 
    [ search index="network" sourcetype="DNS" "Updating DNS information" host_name=MY_COMPUTER 
    | table host_ip 
    | format] 
| transaction host_name 
| eval start_time=_time 
| eval end_time=_time+duration 
| convert ctime(start_time) ctime(end_time)
| search host_name=MY_COMPUTER
| table start_time, end_time, host_name, host_ip
| sort host_ip

Re: Transaction - setting finish time to the start of next transaction

niketn — Fri, 03 Nov 2017 07:50:42 GMT

@eli_mz, you can use streamstats command with current=f and window=1 settings, to get previous/next value of a field provided it is sorted ascending/descending based on time as per your need.

 index="network" sourcetype="DNS" "Updating DNS information" 
     [ search index="network" sourcetype="DNS" "Updating DNS information" host_name=MY_COMPUTER 
     | table host_ip 
     | format] 
| stats first(_time) as start_time by host_ip host_name
| sort - start_time 
| streamstats current=f window=1 last(start_time) as end_time
| eval duration=end_time-start_time
| fieldformat start_time=strftime(start_time,"%c")
| fieldformat end_time=strftime(end_time,"%c")
| table host_ip host_name start_time end_time

You should also try to move away from transaction to stats to improve performance of your query. You can also use min(_time) instead of first(_time) in your stats command.

Re: Transaction - setting finish time to the start of next transaction

eli_mz — Mon, 06 Nov 2017 13:44:45 GMT

Thanks @niketnilay; I believe that should do it. I wasn't aware of the streamstats command; it looks like what I'm after is almost exactly what is shown in example 8 of the documentation of it as well!