How to extract the time elapsed from a value of a field to another value of the same field


(Daniel Gonzalez) #1

Hello!

I have a workflow and I want to know how much time pass from a value to another! For example in the next image with the field "filters" I want to know how much time pass from the value "Creacion" to "Tienda" in that case it seems doesn't pass any second but in the continuing with the workflow for example from "Tienda" to "Capturista" how much time it pass and then from "Capturista" to "Tienda" and so on from one to another!

Just to mention that the workflow will always start with the value "Creacion" but the rest of the values differ from one to another.

My idea was to in the table just put the time elapsed every second value, for example:

filters new field with the time elapsed
Creacion
Tienda --------------- (The time elapsed from "Creacion" to "Tienda")
Capturista ----------- (The time elapsed from "Tienda" to "Capturista")
Analisis Documental ----- (The time elapsed from "Capturista to "Analisis Documental")
Tienda -----------------(The time elapsed from "Analisis documental" to "Tienda)
Capturista --------------- (The time elapsed from "Tienda" to "Capturista")
Analisis Documental ----- (The time elapsed from "Capturista to "Analisis Documental")

And so on

This is my logstash config

input {
file {
path => "/Users/daniel/Documents/TRABAJO/Liverpool/Suburbia-Elastic/SuburbiaRendimientoFabrica20181205.txt"
start_position => "beginning"
sincedb_path => "/dev/null"
}
}

filter {
dissect {
mapping => {"message" => "%{solicitud};%{tienda};%{actions}"}
}

mutate {
gsub => ["actions", ";Tienda", "|Tienda", "actions", ";Creacion", "|Creacion", "actions", ";Capturista", "|Capturista", "actions", ";Analisis Documental", "|Analisis Documental" , "actions", ";APMWorkflow", "|APMWorkflow"]
}

split {
field => "actions"
terminator => "|"
remove_field => ["message"]
}

csv {
source => "actions"
separator => ";"
columns => ["accion", "accion_fecha", "user"]
remove_field => ["actions"]
}

date {

match => [ "accion_fecha", "yyyy-MM-dd HH:mm:ss" ]

}

}

output {
elasticsearch {
hosts => [ "localhost:9200" ]
index => "suburbia"
document_type => "sub"
}

stdout { codec => rubydebug }

}


#2

What does the input file look like? Please do not post screen shots that contain text. Just post the text itself.


(Daniel Gonzalez) #3

Hello Badger!

The txt file contains data like this:

2018259000076;0498;Creacion;2018-09-16 15:24:15;baseuser;Tienda;2018-09-16 15:24:15;MLPARRAF;Capturista;2018-09-16 15:43:07;
2018261000779;0531;Creacion;2018-09-18 19:30:37;baseuser;Tienda;2018-09-18 19:30:37;IRODRIGUEC;Capturista;2018-09-18 19:33:40;
2018298000344;0529;Creacion;2018-10-25 14:06:49;baseuser;Tienda;2018-10-25 14:06:49;KGARCIAF;Capturista;2018-10-25 14:12:22;METENAR;Analisis Documental;2018-10-25 14:16:11;
2018301000808;0535;Creacion;2018-10-28 15:35:55;baseuser;Tienda;2018-10-28 15:35:55;AJMARESR;Capturista;2018-10-28 15:38:40;JCGARCIAS;Tienda;2018-10-28 15:44:19;MFLORESO01;Capturista;2018-10-28 16:12:51;AYHEREDIAH;Tienda;2018-10-28 16:16:09;MFLORESO01;Capturista;2018-10-28 17:54:21;GJAZAMARC;Tienda;2018-10-28 17:56:51;MFLORESO01;Capturista;2018-10-28 18:14:08;ADRAMIREZG03;Tienda;2018-10-28 18:16:43;AJMARESR;Capturista;2018-11-02 19:47:43;MCVEGAS;Tienda;2018-11-02 20:16:42;MFLORESO01;Capturista;2018-11-11 18:03:04;PSALGADO;Tienda;2018-11-11 18:04:13;AJMARESR;Capturista;2018-11-20 19:37:21;JSACOSTAP;Tienda;2018-11-21 09:21:47;SOSANCHEZN;Capturista;2018-11-28 12:46:30;AMARTINEZG16;Tienda;2018-11-28 12:55:47;
2018308001846;0531;Creacion;2018-11-04 19:47:22;baseuser;Tienda;2018-11-04 19:47:22;IRODRIGUEC;Capturista;2018-11-04 19:51:47;AJCRUZR;Tienda;2018-11-04 20:51:27;OBARRIENTOSC;Capturista;2018-11-11 14:21:21;GXRODRIGUEZG;Tienda;2018-11-11 14:27:14;ALOPEZP09;Capturista;2018-11-17 14:20:32;GXRODRIGUEZG;Tienda;2018-11-17 14:24:06;MTPALMAA;Capturista;2018-11-25 14:33:32;LMORAS;Tienda;2018-11-25 14:41:46;IRODRIGUEC;Capturista;2018-11-30 13:19:30;GJAZAMARC;Tienda;2018-11-30 13:20:47;IRODRIGUEC;Capturista;2018-11-30 13:36:27;EVORTEGAV;Tienda;2018-11-30 13:44:50;
2018312001285;0527;Creacion;2018-11-08 17:50:21;baseuser;Tienda;2018-11-08 17:50:21;KBRIONES;Capturista;2018-11-08 17:56:26;AYHEREDIAH;Tienda;2018-11-08 18:03:24;GICASTELANG;Capturista;2018-11-20 10:30:21;JIMACEDOC;Tienda;2018-11-20 10:34:43;FFLUCASO;Capturista;2018-11-23 11:07:10;NJTORRESO;Tienda;2018-11-23 11:22:36;
2018313000103;0495;Creacion;2018-11-09 10:39:34;baseuser;Tienda;2018-11-09 10:39:34;EORTIZS03;Capturista;2018-11-09 11:25:25;JJREYESA;Tienda;2018-11-10 11:57:45;AIHERNANDM01;Capturista;2018-11-10 14:06:40;AYHEREDIAH;Tienda;2018-11-10 14:07:19;SHERNANDEG02;Capturista;2018-11-10 19:02:21;MLSANTANAA;Tienda;2018-11-11 10:27:02;AIHERNANDM01;Capturista;2018-11-11 13:21:54;JAGARCIAS01;Tienda;2018-11-12 10:26:02;AIHERNANDM01;Capturista;2018-11-12 16:30:59;MGARIASG;Tienda;2018-11-12 16:32:54;EORTIZS03;Capturista;2018-11-15 11:09:04;MMANRIQUEZG;Tienda;2018-11-17 14:11:08;EORTIZS03;Capturista;2018-11-18 15:59:01;NJTORRESO;Tienda;2018-11-21 10:51:43;EORTIZS03;Capturista;2018-11-21 12:32:27;JAGARCIAS01;Tienda;2018-11-21 15:14:26;SZAVALAH;Capturista;2018-11-21 15:41:17;JSACOSTAP;Tienda;2018-11-22 08:34:16;SZAVALAH;Capturista;2018-11-22 10:49:40;JCESPINOSAM;Tienda;2018-11-22 10:50:33;EORTIZS03;Capturista;2018-11-23 10:30:18;MPMEZAL;Tienda;2018-11-23 10:32:25;
2018313000265;0523;Creacion;2018-11-09 11:21:54;baseuser;Tienda;2018-11-09 11:21:54;AEAMBROCIOH;Capturista;2018-11-09 11:26:25;JJREYESA;Tienda;2018-11-10 11:52:55;CEMIJANGOSR;Capturista;2018-11-14 20:56:18;EESTRELLAL;Tienda;2018-11-17 13:33:27;CEMIJANGOSR;Capturista;2018-11-26 21:36:26;CCRUZV;Tienda;2018-11-26 21:41:30;CEMIJANGOSR;Capturista;2018-11-28 19:22:34;JFVALDEZE;Tienda;2018-11-28 19:23:22;CEMIJANGOSR;Capturista;2018-11-28 19:32:18;DGARIBAYO;Tienda;2018-11-28 19:33:05;
2018313000303;0535;Creacion;2018-11-09 11:32:26;baseuser;Tienda;2018-11-09 11:32:26;AJMARESR;Capturista;2018-11-09 12:36:09;HMSANCHEZV;Tienda;2018-11-09 12:40:30;SOSANCHEZN;Capturista;2018-11-09 20:56:39;MFPADILLAP;Analisis Documental;2018-11-09 21:00:52;EMEDINAB;Tienda;2018-11-10 09:18:35;SOSANCHEZN;Capturista;2018-11-22 11:29:00;GBCASTROP;Tienda;2018-11-22 11:29:37;
2018313001473;0519;Creacion;2018-11-09 15:30:11;baseuser;Tienda;2018-11-09 15:30:11;RMCAMACHOR;Capturista;2018-11-09 15:40:48;CGROCHA;Tienda;2018-11-09 15:51:27;RMCAMACHOR;Capturista;2018-11-09 20:56:39;NBMORALESP;Tienda;2018-11-09 20:58:24;OADIAZS;Capturista;2018-11-10 14:19:52;GJAZAMARC;Tienda;2018-11-10 14:20:46;RMCAMACHOR;Capturista;2018-11-11 10:50:37;MLSANTANAA;Tienda;2018-11-11 10:57:07;RMCAMACHOR;Capturista;2018-11-11 18:00:31;PBRITOA;Tienda;2018-11-11 18:00:45;OADIAZS;Capturista;2018-11-12 13:03:21;SGZAMORAS;Tienda;2018-11-12 13:05:23;RMCAMACHOR;Capturista;2018-11-14 10:00:34;CEARREDONDOC;Tienda;2018-11-14 10:04:33;RMCAMACHOR;Capturista;2018-11-14 11:15:01;GDIAZG01;Tienda;2018-11-14 11:17:44;OADIAZS;Capturista;2018-11-24 14:52:51;RPMONTANEZG;Tienda;2018-11-24 15:02:03;RMCAMACHOR;Capturista;2018-11-27 12:33:10;MJVALENCIAM;Tienda;2018-11-27 12:33:47;
2018313001657;0519;Creacion;2018-11-09 16:02:55;baseuser;Tienda;2018-11-09 16:02:55;RMCAMACHOR;Capturista;2018-11-09 16:05:59;KLHERNANDEZ;Tienda;2018-11-10 10:45:24;OADIAZS;Capturista;2018-11-10 14:27:28;JJREYESA;Tienda;2018-11-10 15:50:13;OADIAZS;Capturista;2018-11-12 12:53:59;JJREYESA;Tienda;2018-11-12 13:21:31;RMCAMACHOR;Capturista;2018-11-14 10:49:11;MCVEGAS;Tienda;2018-11-15 20:16:00;RMCAMACHOR;Capturista;2018-11-20 20:13:15;SMPEREZS;Tienda;2018-11-20 20:14:23;MMBAYARDOS;Capturista;2018-11-20 20:22:09;GBCASTROP;Tienda;2018-11-21 08:59:33;RMCAMACHOR;Capturista;2018-11-21 11:35:17;JCESPINOSAM;Tienda;2018-11-21 11:35:49;RMCAMACHOR;Capturista;2018-11-25 14:18:13;LFLEMUSC;Tienda;2018-11-25 14:23:36;
2018313001779;0589;Creacion;2018-11-09 16:28:52;baseuser;Tienda;2018-11-09 16:28:52;MSJUAREZE;Capturista;2018-11-09 16:34:13;LSVELAZQUEZL;Tienda;2018-11-10 10:44:06;IMARTINEZD01;Capturista;2018-11-10 13:42:13;GDIAZG01;Tienda;2018-11-10 13:42:45;LBBRINGASO;Capturista;2018-11-12 12:56:52;JJREYESA;Tienda;2018-11-12 13:22:18;IMARTINEZD01;Capturista;2018-11-12 18:53:24;METENAR;Tienda;2018-11-14 09:25:41;PMONTOYAG;Capturista;2018-11-18 20:26:27;SGAONAC;Tienda;2018-11-21 16:33:03;AMANGELESP;Capturista;2018-11-25 13:06:43;LOROZCO;Tienda;2018-11-25 13:07:32;AMANGELESP;Capturista;2018-11-27 13:48:49;GJAZAMARC;Tienda;2018-11-27 13:49:01;AMANGELESP;Capturista;2018-11-27 15:53:31;DGARIBAYO;Tienda;2018-11-27 15:54:04;AMANGELESP;Capturista;2018-11-28 11:00:12;CIGARCIAG;Tienda;2018-11-28 11:01:59;AMANGELESP;Capturista;2018-11-29 17:55:19;DCARAPIAL;Tienda;2018-11-29 17:56:18;

#4

I think you are getting lines joined there. Please edit your post and either select the text and click on </> in the toolbar above the edit pane, or precede and follow the text with a line contain three backticks: ```


(Daniel Gonzalez) #6

Ready! I change to the </>


#7

OK. That's more complicated than I was expecting. I can take a look tomorrow (about 16 hours from now).


(Daniel Gonzalez) #8

Ok Badger!

Thanks a lot for your help

:wink:


#9

The file input, dissect and mutate look fine. I would not use a split filter at that point because that creates separate events, and you lose any association between events on the same line. Instead use a mutate and split to create an array that you can work on.

mutate {
    split => { "actions" => "|" }
    remove_field => ["message"]
}

grok { match => { "actions" => [ "%{WORD:action};%{DATA:ts};(?<user>[^;]*)$" ] } }

That grok takes the array and matches each entry. It returns three arrays - action, ts, and user. The last entry on each line does not have a user after the ;, so that array will have one less entries than the other arrays.

Although grok works on array, the date filter does not. You cannot pass it an array of strings and get back an array of dates in target, so we dive down into ruby that this point.

ruby {
    code => "

I use " to surround the code and ' within it. That's a personal preference, some folks prefer it the other way around. First we iterate over the ts array that grok returned and parse the timestamps.

        ts = event.get('ts')
        timestamps = []
        if ts then
            ts.each { |x|
                timestamps << Time.strptime(x, '%Y-%m-%d %H:%M:%S')
            }
        end
        # event.set('timestamps', timestamps)

Then for each timestamp except the first we calculate the delta. We get back a number in seconds.

        deltas = []
        timestamps.each_index { |x|
            if x == 0 then
                deltas << 0
            else
                deltas << timestamps[x].to_i - timestamps[x-1].to_i
            end
        }
        # event.set('deltas', deltas)

Now we have arrays for action, user, timestamp, and delta. It is probably useful to combine them. You could trivially add tienda or solicitud or other fields from the main event to each object.

        tienda = event.get('tienda')
        action = event.get('action')
        user = event.get('user')
        a = []
        action.each_index { |k|
            n = {
                'action'    => action[k],
                'user'      => user[k],
                'delta'     => deltas[k],
                'timestamp' => timestamps[k],
                'tienda'    => tienda
            }
            a << n
        }
        event.set('arrayOfObjects', a)

Here ends the ruby filter.

        # event.remove('ts')
        # event.remove('user')
        # event.remove('action')
        # event.remove('actions')
    "
}

At this point you may want to use a split on arrayOfObjects, since each object now has all the data in it.

"arrayOfObjects" => [
    [0] {
           "action" => "Creacion",
             "user" => "baseuser",
            "delta" => 0,
        "timestamp" => 2018-09-16T19:24:15.000Z,
           "tienda" => "0498"
    },
    [1] {
           "action" => "Tienda",
             "user" => "MLPARRAF",
            "delta" => 0,
        "timestamp" => 2018-09-16T19:24:15.000Z,
           "tienda" => "0498"
    },
    [2] {
           "action" => "Capturista",
             "user" => nil,
            "delta" => 1132,
        "timestamp" => 2018-09-16T19:43:07.000Z,
           "tienda" => "0498"
    }

(Daniel Gonzalez) #10

Badger it works fine!

So kind from your part, thank's a lot for your time and help!

Regards!


(system) closed #11

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.