How to have Splunk recognize %Y%m%d%H%M%S formatted date time?

[sakeebhossain]

I would to create charts using timestamped data. I have a CSV file representing a table which has a TRANSACTIONDATETIME column ( a datetime in format %Y%m%d%H%M%S) and a TRANSACTION_COUNTRY column (just a 3 letter code representing a country). I would like to have Splunk create a line graph showing, with a line for each unique TRANSACTION_COUNTRY, reporting frequency of at each second. I tried the following search:

source="data.csv" |     eval _time=strptime(TRANSACTIONDATETIME, "%Y%m%d%H%M%S") | timechart span=1s count  

However, it does not work. I'm sure this is a simple fix, I would appreciate if someone could help me out.

[briancronrath]

I think you'll have to use gentimes to dynamically call an earliest and a latest timerange for your timechart. And then also do your timechart by CODE in order to seperate it out by country. Other than that you have it pretty much complete.

[niketn · Accepted solution]

@sakeebhossain... Can you please add an example of date TRANSACTIONDATETIME from data.csv? Also whether single digit months and dates in CSV remain as single digit or prefixed with zeros? Similarly for Hour and Minutes fields whether single digits are prefxied with zeros or not.

Following is a run anywhere example which works fine for me:

| makeresults 
| eval TRANSACTIONDATETIME= "20160314012436"
| eval _time=strptime(TRANSACTIONDATETIME, "%Y%m%d%H%M%S") 
| table _time TRANSACTIONDATETIME

[sakeebhossain]

This is what the table looks like:
+--------------------------+-----+
| TRANSDATETIME |CODE |
+------------------------+-------+
|20161205105707 | CAN |
+------------------------+-------+
|20161205105707 | CAN |
+------------------------+-------+
|20161205105708 | USA |
+------------------------+-------+
|20161205105709 | CAN |
+------------------------+-------+

Your example works fine for me. What I'd like is for Splunk to get the TRANSDATETIME from and use it on the x-axis of my timechart, i.e. _time. I want to plot the frequency of of requests for each country per second. The issue is that it seems that Splunk is not recognizing the %H%M%S part of in the strptime() of my query.

[niketn]

I dont see why it would not work, based on sample you sent, following run anywhere example works as expected for me (last two lines are strptime while remaining is to generate mock data. The same would work even if I upload the data as CSV. If for some reasons you want to use TRANSACTIONTIME column from your csv as the timestamp field then please use the sourcetype below(you can generate and the same yourself using Data Preview mode in Splunk while uploading a sample CSV data). It is better to have Splunk identify timestamp correctly upfront than overriding later durin search time.:

| makeresults
| eval TRANSACTIONDATETIME= "20161205105707"
| eval CODE="CAN"
| append [| makeresults
| eval TRANSACTIONDATETIME= "20161205105707"
| eval CODE="CAN"]
| append [| makeresults
| eval TRANSACTIONDATETIME= "20161205105708"
| eval CODE="USA"]
| append [| makeresults
| eval TRANSACTIONDATETIME= "20161205105709"
| eval CODE="CAN"]
| eval _time=strptime(TRANSACTIONDATETIME, "%Y%m%d%H%M%S") 
| table _time TRANSACTIONDATETIME CODE

Upload a sample csv file to Splunk to set the Sourcetype in Preview mode. Choose Advanced Timestamp Extraction and provide Timestamp format as %Y%m%d%H%M%S and Timestamp fields as TRANSDATETIME. (refer to Splunk docs for uploading a sample csv https://docs.splunk.com/Documentation/Splunk/latest/PivotTutorial/GetthetutorialdataintoSplunk)

[YourSourceTypecsv]
...
...
TIME_FORMAT=%Y%m%d%H%M%S
TIMESTAMP_FIELDS=TRANSDATETIME

[alemarzu]

Hi there,

Are you looking something like this ?

| inputlookup data.csv | timechart span=1sec count by TRANSACTION_COUNTRY