Splunk Search

Transaction - setting finish time to the start of next transaction

eli_mz
Explorer

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

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

eli_mz
Explorer

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!

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