topic Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value? in Splunk Search

How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

justsshary — Thu, 22 Sep 2016 21:16:06 GMT

Hi,
I am trying to extract sequence of events from logs by using transaction command. I am looking for sequence of six events. It works well as long as the first and the last events are not the same but when they are same, it just lists every line in the log individually matching the value in the startswith/endswith.

Here is my search :

index=myindex source=/my/folder/raw.log.gz xxx.xxx.xxx.xxx | sort -Time | eval ip="xxx.xxx.xxx.xxx" | transaction ip startswith="win_event=4624" endswith="win_event=4624" maxevents=6| search eventcount=6

In this search, I am searching for all the logs for an IP, sorting them (as transaction returns an error if not sorted), creating an IP field as it is not there, and generating a transaction for this IP to extract log lines that have win_event=4624 as starting and ending event. I want all the six events including 4624 that occur between two 4624. This search only works when startswith and endswith do not have the same value.

I also tried the following variation but it didn't work either:

| sort -Time | eval ip="xxx.xxx.xxx.xxx" | streamstats window=6 current=true last(win_event) AS win_event_last first(win_event) AS win_event_first | transaction startswith="win_event_first=4624" endswith="win_event_last=4624" maxevents=6

Or is it possible to list only the records (log lines) that occur one after each other in a order with the given events for an ip? E.g., if 4624, 4434, 4124, 4771, 1006, 4264 all appear one after another with the same ip on different log records.

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

somesoni2 — Fri, 23 Sep 2016 02:59:19 GMT

Can we have some sample (may be mock) events?

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

inventsekar — Fri, 23 Sep 2016 03:00:56 GMT

not sure, but, may be try this -

| transaction startswith=eval(match(_raw, "win\_event\_first\=4624")) endswith="win_event_last=4624"

| transaction startswith=eval(match(_raw, "win\_event\_first\=\d{4}")) endswith=(match(_raw, "win\_event\_first\=\d{4}"))

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

justsshary — Fri, 23 Sep 2016 03:05:51 GMT

192.168.1.2 4444
192.168.100.1 4624
192.168.100.1 4624
192.168.1.1 5000
192.168.100.1 4624
192.168.100.1 4434
192.168.100.1 4124
192.168.100.1 4471
192.168.100.1 1006
192.168.100.1 4624

See above example, I am interested in getting the sequence of log records for the IP: 192.168.100.1 starting from 4624 and ending at 4624 with a total of six events. This means the last six lines in the above case.

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

mIliofotou_splu — Fri, 23 Sep 2016 04:42:45 GMT

I assume that want you want to do here is create a sliding window of 6 events and select those windows that start with 4624 and end with 4624. Given that you cannot do this with transaction (if you use the same startswith and endswith it gives all transactions to be single events), I will try to show you how you can do this using streamstats.

index=myindex source=/my/folder/raw.log.gz xxx.xxx.xxx.xxx | sort -Time | eval ip="xxx.xxx.xxx.xxx" | streamstats list(win_event) as event_window window=6 by ip | eval window_len=mvcount(event_window) |  eval window_first=mvindex(event_window, 0) | eval window_last=mvindex(event_window, 5)  | where window_len = 6 AND window_first=4624 AND window_last=4624

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

justsshary — Fri, 23 Sep 2016 22:39:39 GMT

It is only returning individual lines with the given last event.

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

mIliofotou_splu — Sat, 24 Sep 2016 19:54:37 GMT

Yes, It will be individual lines. Each of those lines will be the ending of sequence of 6 events where the starting event was a login 4624 and the last one (the current event you see) is the next 4624 login.

Do you need all the raw events? I though you only needed the event codes between and the fact that such sequences exist. Both of those are given by the SPL above. If you need the raw events you might want to use a transaction instead of a "where" command.

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

justsshary — Sun, 25 Sep 2016 01:49:11 GMT

Yes, I need all the logs records for all the events.

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

mIliofotou_splu — Mon, 26 Sep 2016 16:50:47 GMT

You can keep your raw events by adding an extra list(_raw) as other_events next to list(win_event). This way, when you detect the last event of the window you will have the remaining events of the window under other_events. In the meanwhile, I will try to see if there is a better way to do this.

Re: How to modify my transaction search to extract a sequence of six events when startswith and endswith have a matching value?

justsshary — Tue, 27 Sep 2016 22:33:44 GMT

Thanks, list(_raw) helps and it is the closest to what i was looking for. But it turned out that when the timings are same for multiple events then in that case I need a solution outside of splunk.