topic Re: how to assign csv file column value as event timestamp? in Getting Data In

how to assign csv file column value as event timestamp?

dhavamanis — Mon, 28 Sep 2020 17:39:23 GMT

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,xxxxxx99@aol.com,,,,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,xxxxxxxxx9999@gmail.com,,,,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".

Re: how to assign csv file column value as event timestamp?

pradeepkumarg — Mon, 22 Sep 2014 15:59:56 GMT

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

Re: how to assign csv file column value as event timestamp?

dhavamanis — Mon, 28 Sep 2020 17:39:25 GMT

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.

Re: how to assign csv file column value as event timestamp?

aweitzman — Mon, 22 Sep 2014 16:20:19 GMT

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

Re: how to assign csv file column value as event timestamp?

dhavamanis — Mon, 28 Sep 2020 17:40:24 GMT

Re: how to assign csv file column value as event timestamp?

aweitzman — Tue, 23 Sep 2014 19:20:20 GMT

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.

Re: how to assign csv file column value as event timestamp?

dhavamanis — Tue, 23 Sep 2014 19:36:06 GMT

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".

Re: how to assign csv file column value as event timestamp?

aweitzman — Tue, 23 Sep 2014 19:59:43 GMT

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.

Re: how to assign csv file column value as event timestamp?

dhavamanis — Thu, 09 Oct 2014 23:10:15 GMT

its working fine for me. Thank you.