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!
What's the problem with output of set diff command?
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")