Splunk Search

first connexion and last connexion VPN

numeroinconnu12
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 "debut_cnx" column and the end date of the connection in the "fin_cnx" column. Can you help me please.

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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

to4kawa
Ultra Champion

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?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...