Splunk Search
Highlighted

first connexion and last connexion VPN

Path Finder

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

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

0 Karma
Highlighted

Re: first connexion and last connexion VPN

Ultra Champion

The both "debutcnx" and "fincnx" are same value.
In this picture,
debutcnx: 04/03/2020 11:39:05
fin
cnx: 04/03/2020 18:37:56

Is this correct?

0 Karma
Highlighted

Re: first connexion and last connexion VPN

Influencer

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 startdate and enddate 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
0 Karma