Getting Data In

how to assign csv file column value as event timestamp?

dhavamanis
Builder

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

Tags (3)
1 Solution

aweitzman
Motivator

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

View solution in original post

aweitzman
Motivator

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
Builder

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.

0 Karma

aweitzman
Motivator

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.

0 Karma

dhavamanis
Builder

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

0 Karma

aweitzman
Motivator

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
Builder

its working fine for me. Thank you.

0 Karma

dhavamanis
Builder

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.

0 Karma

pradeepkumarg
Influencer

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

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...