first connexion and last connexion VPN

[numeroinconnu12]

Hello, this is my request

index=juniper_vpn ID=AUT24803
( src_user!=ANONYMOUSUSER*)
| eval src_user=upper(src_user)
| eval lmt=min(_time), date=max(_time)
| convert timeformat="%d/%m/%Y %H:%M:%S." ctime(lmt) AS lmt, ctime(date) AS date
| join type=left user [| inputlookup accounts.csv |search domaine="intra"| eval user=matricule ]
| join type=left ua [| inputlookup dirigeant.csv| eval ua=UA ]
| rename user as Utilisateur, cn as Nom, ua as UA, samaccountname as Dirigeant
| dedup Utilisateur
| rename src_user as Matricule
| stats count(Utilisateur) as "Total", list(Nom) as Nom, list(date) as fin_cnx, list(lmt) as debut_cnx, list(Matricule) as Matricule by UA
| table UA Nom Matricule debut_cnx fin_cnx Total
| sort UA
| addcoltotals labelfield=UA label="nombre total d'utilisateurs" Total

You can see my results in picture.

I'd like to have the start date of the connection in the "debut_cnx" column and the end date of the connection in the "fin_cnx" column. Can you help me please.

[manjunathmeti]

Hi @numeroinconnu123,

Try this:

index=juniper_vpn ID=AUT24803 ( src_user!=ANONYMOUSUSER*) | eval src_user=upper(src_user) | eval lmt=min(_time), date=max(_time) | convert timeformat="%d/%m/%Y %H:%M:%S." ctime(lmt) AS lmt, ctime(date) AS date | join type=left user [| inputlookup accounts.csv |search domaine="intra"| eval user=matricule ] | join type=left ua [| inputlookup dirigeant.csv| eval ua=UA ] | rename user as Utilisateur, cn as Nom, ua as UA, samaccountname as Dirigeant | dedup Utilisateur | rename src_user as Matricule | stats count(Utilisateur) as "Total", list(Nom) as Nom, list(date) as fin_cnx, list(lmt) as debut_cnx, list(Matricule) as Matricule, earliest(date) as start_fin_cmx, latest(lmt) as last_debut_cnx by UA | table UA Nom Matricule debut_cnx fin_cnx Total | sort UA | addcoltotals labelfield=UA label="nombre total d'utilisateurs" Total

If you want start_date and end_date common for all values in field UA then try this,

index=juniper_vpn ID=AUT24803 ( src_user!=ANONYMOUSUSER*) | eval src_user=upper(src_user) | eval lmt=min(_time), date=max(_time) | convert timeformat="%d/%m/%Y %H:%M:%S." ctime(lmt) AS lmt, ctime(date) AS date | join type=left user [| inputlookup accounts.csv |search domaine="intra"| eval user=matricule ] | join type=left ua [| inputlookup dirigeant.csv| eval ua=UA ] | rename user as Utilisateur, cn as Nom, ua as UA, samaccountname as Dirigeant | dedup Utilisateur | rename src_user as Matricule | stats count(Utilisateur) as "Total", list(Nom) as Nom, list(date) as fin_cnx, list(lmt) as debut_cnx, list(Matricule) as Matricule, earliest(date) as start_fin_cmx, latest(lmt) as last_debut_cnx by UA | table UA Nom Matricule debut_cnx fin_cnx Total | eventstats earliest(start_fin_cmx) as start_fin_cmx, latest(last_debut_cnx) as last_debut_cnx | sort UA | addcoltotals labelfield=UA label="nombre total d'utilisateurs" Total

[to4kawa]

The both "debut_cnx" and "fin_cnx" are same value.
In this picture,
debut_cnx: 04/03/2020 11:39:05
fin_cnx: 04/03/2020 18:37:56

Is this correct?