topic Re: How to join 2 CSV files that have unique values in single table? in Splunk Search

How to join 2 CSV files that have unique values in single table?

krishnacasso — Thu, 13 Oct 2016 20:59:11 GMT

Fields in first.csv file: DN, uidn, count, Status, TimeStamp
Fields in second.csv file: DN, uidn, AppID, eid, user, eStatus, Timestamp
DN value is different in both the tables, so I want to rename as DN2 and show it on a table.
Both the CSV files have unique uidn values. I want to create a single table joining all the fields,

Table:
uidn,DN,DN2,count,Status,TimeStamp,eid,user

Thanks.

Re: How to join 2 CSV files that have unique values in single table?

lguinn2 — Thu, 13 Oct 2016 21:12:50 GMT

This should work

source=csv1 OR source=csv2
| eval DN2 = if(source=="csv2",DN,null())
| eval DN = if(source=="csv1",DN,null())
| stats list(DN) as DN, list(DN2) as DN2, count, list(Status) as Status, 
     list(TimeStamp) as TimeStamp, list(eid) as eid, list(user) as user by uidn

Or if you really want "all fields"

source=csv1 OR source=csv2
| eval DN2 = if(source=="csv2",DN,null())
| eval DN = if(source=="csv1",DN,null())
| stats list(*) as * by uidn

Re: How to join 2 CSV files that have unique values in single table?

cmerriman — Thu, 13 Oct 2016 21:14:58 GMT

Is the join on uidn and timestamp?

|inputlook first.csv |join uidn timestamp [|inputlookup second.csv|rename DN as DN2]