topic Re: Very odd timestamp - how can I extract? in Splunk Search

Very odd timestamp - how can I extract?

himynamesdave — Tue, 02 Jun 2015 20:07:39 GMT

Hi all - I need to "build" a timestamp from an event. The events are fixed format, meaning timestamp variables will always appear in the same place in each event. However, I am not sure how I can write an extraction to deal with this.

Here is a sample event (also attached to question to ensure formatting).

  A       201312    13064           34                2                             34105                                            122  2    07  Y2 7                          31  0502 2   073          01  1    2                         09042                      0805  273  Y00000000    N                  NNNNNNN NXXN00    2   222       NN NNNNNNN  2 2  NNNNN NNNYNN 1     2  2       11X    221 1NNNNNN3N22 094885  1            F 03  201339   40082       2722    063  NNNNNNN         NNNNNNNNNNNN22       2    2  2 2  NYY23.32888881730817308140167659           11111111111111111111 1111111 111111111111 111111  1111111111111111111111111111 1   11              1  111111111    1   111            1 1      1     1  1            11                11       1    1  1 1

The variables that make up the time can be found in the event in the following places:

position - unit
...14 - blank (or not related)
15-18 - year
19-20 - month (values = 1-12 [1 = jan])
21-24 - blank
25-28 - time (24hr/min)
29 - day (values = 1-7 [1 = sunday])

So in the above event the time would be: 2013 (2013), December (12), 13h06m (1306), Thursday (4)

To add some context - this is birth record data. you cannot find the exact day of a month, just the day of the month someone was born.

I just cannot get a match in my extraction 😞 Can anyone provide any guidance towards a nice way to extract this time for Splunk?

Re: Very odd timestamp - how can I extract?

lguinn2 — Tue, 02 Jun 2015 20:13:53 GMT

In props.conf, try

TIME_PREFIX=.{14}
TIME_FORMAT=%Y%m       %H%M%d

Re: Very odd timestamp - how can I extract?

himynamesdave — Tue, 02 Jun 2015 20:58:25 GMT

Hi Lisa, thanks! Sadly this doesn't work.

My problem is the "day" field. Instead of %d in your answer I tried %w. However, %w expects 0-6 (0=Sunday). In my events my days count from 1-7 (1=Sunday). Any easy way to fix this?

Re: Very odd timestamp - how can I extract?

davebrooking — Wed, 03 Jun 2015 06:07:53 GMT

Hi Dave

Could you do as Lisa suggests and use the %d in the time format, and also define a calculated field that would translate your value of 1-7 into the day of the week.

Add a stanza into props.conf (I don't have an instance of Splunk to test this at the moment) of the form

<Stanza>
Eval-YourField = case(date_mday==1, "Sunday", date_mday==2,"Monday", ...)

Re: Very odd timestamp - how can I extract?

lguinn2 — Wed, 10 Jun 2015 05:41:46 GMT

Are you saying that you don't have a day of the month (1-31) in the log file?!!

Also @davebrooking made a good suggestion for a getting a day of the week. In fact, you can even ask Splunk to calculate that for you, giving you either a number or the actual day - for any search. For example

yourseachhere
| eval dow_number = strftime(_time,"%A")
| eval dow_name = strftime(_time,"%u")
| eval timestamp = strftime(_time"%x %X")
| table timestamp dow_name dow_number

So there is really no reason to worry about the day of week at input time. And the dow_number above is 1-7 where Monday=1

Re: Very odd timestamp - how can I extract?

crystaltransfer — Wed, 10 Jun 2015 07:33:45 GMT

really odd, i can't get it