topic Re: dedup problem in Splunk Search

dedup problem

numeroinconnu12 — Wed, 11 Mar 2020 22:43:47 GMT

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?

Re: dedup problem

numeroinconnu12 — Thu, 12 Mar 2020 07:56:24 GMT

No answer ? :'(

Re: dedup problem

to4kawa — Thu, 12 Mar 2020 08:02:48 GMT

| 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

Re: dedup problem

pradeepk50 — Wed, 30 Sep 2020 04:37:22 GMT

Hi ,

Try to use dedup before the 6th step.

Let me know if that works 🙂

Thanks