Splunk Search

dedup problem

numeroinconnu12
Path Finder

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

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

0 Karma

pradeepk50
Loves-to-Learn

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

0 Karma

to4kawa
Ultra Champion
| 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
0 Karma

numeroinconnu12
Path Finder

No answer ? :'(

0 Karma
Get Updates on the Splunk Community!

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...

4 Ways the Splunk Community Helps You Prepare for .conf25

.conf25 is right around the corner, and whether you’re a first-time attendee or a seasoned Splunker, the ...

Enhance Your Splunk App Development: New Tools & Support

UCC FrameworkAdd-on Builder has been around for quite some time. It helps build Splunk apps faster, but it ...