dedup problem

[numeroinconnu12]

Hello,

This is my query with " dedup Matricule"

index=juniper_vpn  (ID=AUT22673 OR ID=AUT24803) ......67
| eval src_user=upper(src_user)
| join type=left user [| _accounts.csv |search domaine="intra"| eval user=matricule ]
| join type=left ua [|_dirigeant.csv | eval ua=UA ]
| rename user as Matricule, cn as Nom, ua as UA
| dedup Matricule
| stats min(_time) as Firstdate max(_time) as Lastdate list(Nom) as Nom list(UA) as UA list(Matricule) as Matricule by src_user
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(Firstdate) AS Firstdate, ctime(Lastdate) AS Lastdate 
| eval Date=if(Firstdate = Lastdate,"le ".Firstdate,"connecté entre le ".Firstdate." et le ".Lastdate) 
| stats count(Matricule) as "Total" list(Nom) as Nom list(Date) as "Date et heure de connexion" list(src_user) as Utilisateur by UA
| table UA Nom Utilisateur "Date et heure de connexion" "Total"
| addcoltotals labelfield=UA label="nombre total d'utilisateurs" "Total"

you can see the results in the screenshot below.

The results in the column "Date et heure de connexion" are not correct.

But if I delete "dedup Matricule" from my query, the results obtained in the column "Date et heure de connexion" are correct but they are many as you can see on the second screenshot.
How can I get only one value in the "Date et heure de connexion" column without dedup? Can you please help me?

[pradeepk50]

Hi ,

Try to use dedup before the 6th step.

index=juniper_vpn (ID=AUT22673 OR ID=AUT24803) ......67
| eval src_user=upper(src_user)
| join type=left user [| _accounts.csv |search domaine="intra"| eval user=matricule ]
| join type=left ua [|_dirigeant.csv | eval ua=UA ]
| dedup user
| rename user as Matricule, cn as Nom, ua as UA

Let me know if that works 🙂

Thanks

[to4kawa]

| stats count(Matricule) as "Total" list(Nom) as Nom list(Date) as "Date et heure de connexion" list(src_user) as Utilisateur by UA
→
| stats count(Matricule) as "Total" list(Nom) as Nom values(Date) as "Date et heure de connexion" list(src_user) as Utilisateur by UA

[numeroinconnu12]

No answer ? :'(