Splunk Search

strptime() format based on multiple fields

rahulvairagyam
New Member

I Have two fields one with Date in YYYYMMDD and TIME in HHMMSS format. the hour field sometime has values like 3000 which means it is 00:30:00 AM i,e it has no preceding zeroes. I want to index based on these two fields while ingestion. Can you please help me how can i achieve this exactly.

Tags (1)
0 Karma

gvmorley
Contributor

Hi,

Using your sample data as a .csv, you can do something like this at search time:

| inputlookup timetest.csv
| eval newtime="000000".INT_TIME_DIM_SEQ
| eval newtime=substr(newtime,-6)
| eval datetime=INT_DT_DIM_SEQ." ".newtime
| eval datetime=strptime(datetime,"%Y%m%d %H%M%S")
| eval datetime=strftime(datetime,"%d-%m-%Y %H:%M:%S")

Given that's the case, it would most likely be possible to do that extractions in props.conf

[mysourcetype]
EVAL-datetime = strptime(INT_DT_DIM_SEQ." ".substr("000000".INT_TIME_DIM_SEQ,-6),"%Y%m%d %H%M%S")

Note, this is just at 'search time'.

If your data is some sort of log file, then you could set the index time to the current time. I.e. the time that it was ingested, based on that of the Indexer. So in props.conf

[mysourcetype]
DATETIME_CONFIG = CURRENT

Then when you write your searches and reports, you can use the new 'datetime' field to order them. Or just use:

| eval _time=datetime

to replace the original time with the one you want.

Again, this is only at search time, it doesn't change the value for _time in the Index.

I'm pretty sure this isn't exactly what you want, but maybe will do as a workaround for you?

0 Karma

rahulvairagyam
New Member

DT_1,FACT_SEQ,INT_DT_DIM_SEQ,INT_TIME_DIM_SEQ,SMPL_TIME_DT_DIM_SEQ,SMPL_TIME_TIME_DIM_SEQ,MACHINE,DELTA_TIME
7-Jun-12,1188999,20120607,111500,20120607,41500,5724,189
7-Jun-12,1188485,20120607,3000,20120607,41500,1234,189

I need to get the timestamps created based on INT_DT_DIM_SEQ, INT_TIME_DIM_SEQ fields. The problem is INT_TIME_DIM_SEQ has values like 221500 which is 22:15:00 and sometime 3000 which is 00:30:00 and 15000 which is 01:50:00. I tried TimeFormat %Y%m%d%H%M%S and time fields as INT_DT_DIM_SEQ,INT_TIME_DIM_SEQ but it is taking incorrect times in cases where there are no leading zeroes in the data feed.

0 Karma

aaraneta_splunk
Splunk Employee
Splunk Employee

@rahulvairagyam - Were you able to test out gvmorley's solution? Did it work? If yes, please don't forget to resolve this post by clicking on "Accept". If you still need more help, please provide a comment with some feedback. Thanks!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Sample events please?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...