how to assign csv file column value as event timestamp?

[dhavamanis]

We have csv file dump and its contains the user profile data with column Account_Creation_Date (sample data : "2008-07-17 00:00:00"). We need to keep this column value as event time. can you please guide me how to do that?.

Sample Event in dump and forwarder will pass these data from different system:

id,brand_id,Primary Email,Email_Verified,Facebook_ID,Twitter_ID,Username,Gender,Birth_Month,Birth_Day,Birth_Year,Prefix,First_Name,Middle_Name,Last_Name,Suffix,Primary Address_Line 1,Primary Address_Line 2,Primary Address_City,Primary Address_State,Primary Address_ZIP,Primary Address_Country,Primary Address_Primary,Primary Address_Type,Primary_Phone,Mobile_Phone,Last_Log_In,Account_Creation_Date,Last_Account_Update_Date,Brand_Data

00000a4b53ee4a8da9cb6c8ff3699031,af572e378e87458b8fcffc01df00a3dc,[email protected],,,,xxxxxx99,m,3,23,1994,,,,,,,,Philadelphia,PA,19114,US,True,,,,2012-11-27 00:00:00,2012-11-27 00:00:00,2012-11-27 00:00:00,

00004ace934c476099c132092097d9ba,af572e378e87458b8fcffc01df00a3dc,[email protected],,,,xxxxxxxxx9999,f,9,8,1986,,,,,,,,los angeles ,CA,90043,US,True,,,,2012-11-24 00:00:00,2012-11-24 00:00:00,2012-11-24 00:00:00,

Can you give me the sample entry for props.conf. these events are indexing into sourcetype=csv and index="idxmember".

[aweitzman · Accepted solution]

You need to pull the time out before the data is indexed if you want it to be the event time. See this page for how to do that: http://docs.splunk.com/Documentation/Splunk/6.1.3/Data/Configuretimestamprecognition

[dhavamanis]

we want this Account_Creation_Date column value in event date, as of now its showing event date as indexing time. If i have this column value in event date, we can able to filter records easily in pivot / time picker. can you please provide the sample for this.

[aweitzman]

You do not have a column value at this point yet, since assigning the event time happens before field extraction. Please follow the directions in the link I provided to either (a) use the interactive data previewer to describe where your date is, or (b) write a stanza in props.conf using a TIME_PREFIX regex to figure out where in your line the time is, and then the TIME_FORMAT strptime() format to describe the time contained there.

[dhavamanis]

Thanks Aweitzman, we have updated the sample events in the original request, Can you please give me the sample entry for props.conf. these events are indexing into sourcetype=csv and index="idxmember".

[aweitzman]

So something like this might work:

[csv]
TIME_PREFIX = (?:(?:.*?),){26}
TIME_FORMAT = %Y-%m-%d %H:%M:%S

If not, try tweaking it a bit until it does. Remember, you need to restart your server after making this change for it to go into effect.

[dhavamanis]

its working fine for me. Thank you.

[dhavamanis]

we want this Account_Creation_Date column value in event date, as of now its showing event date as indexing time. If i have this column value in event date, we can able to filter records easily in pivot / time picker.

[pradeepkumarg]

If I understood it correct, what you are looking for is 'auto lookup'. More details here
http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchTutorial/Usefieldlookups