Getting Data In

How to spot differences in two names in a list in CSV files?

jcadena
New Member

I'm having a hard time coming up with the right query or search. My dilemma is I have 2 separate lists containing names, I need to compare these 2 lists and spot differences, extra names, or missing names. The problem is that the name format is not same, one contains last name, middle, firstname, the other first n, last n, middle, maiden name. Let's just say the format is not uniform. If I can do a query based on first and lastname and output results that don't match that query I'm gold. The only field I have to go by is the name, I've tried the diff command without much success

|set diff [search source="India Physical Access List.csv" | stats count by "display name" | table "display name"] [search source="India HR Active Roster.csv"  | stats count by "display name" | table "display name"]

Sample CSV view:
File 1
display name,upn,country,location,user id,manufacturer
Aaleti Praveen  Kumar,,India,India Corp,,
Achyutana Subramanyam  ,,India,India Corp,,


File 2
display name,upn,country,location,user id,manufacturer
Krishna Kumar V,,India,Hyderabad,7,
Rajavardhan Reddy Kundur,,India,Hyderabad,9,
Venkatraman Krishnamurthy,,India,Hyderabad,24,

Any help is greatly appreciated. Thanks!

Tags (2)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

What's the problem with output of set diff command?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Also, see if this gives you any better result (assuming comparison is done based on "India HR Active Roster.csv" file)

source="India Physical Access List.csv" OR source="India HR Active Roster.csv"
| table source "display name" | eval name=split('display name'," ") | nomv name
| stats values(source) as sources by name
| eval result=case(mvcount(sources)=2,"Present in Both",source="India Physical Access List.csv","Missing in India HR Active Roster",true(),"New in India HR Active Roster")
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...