Splunk Search

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

sakeebhossain
Explorer

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.

0 Karma
1 Solution

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

briancronrath
Contributor

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.

0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

sakeebhossain
Explorer

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.

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

alemarzu
Motivator

Hi there,

Are you looking something like this ?

| inputlookup data.csv | timechart span=1sec count by TRANSACTION_COUNTRY
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...