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!

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...