How to properly index data from a CSV input with timestamp column

[jayakumar89]

I'm trying to index CSV format inputs and the timestamp can be indicated by the fields within, rather than the time that the data actually was ingested. The only time field is in a format of “HHMMSS” . I was able to set this up and get it to work when all the numbers are present, however instead of zeros this format just has a blank. For example, 9:30AM would be 93000 and 12:15AM would be 1500. When this happens (blanks instead of leading zeros) the time is not read in correctly. it would be better if it was just ingested in the right time so a lookup does not have to be done to find the timestamp. Is there a way to read the time correctly?

[gvmorley]

Hi,

Another option you could do, assuming your data is more of a 'one off' that continually updating, is just fix the format in Excel or Google Sheets.

So something like:

=TEXT(cell_ref, "000000")

Which gives you:

I find sometimes that it's easier to fix-up the data first, before Indexing it into Splunk.

It's not always possible, depending on how your data is flowing in, but if you can, then tweak it first.

The 3rd column was just to check how the Padded string would be interpreted (or as an easier to read column in the data). The formula for this is:

=TIME(LEFT(cell_ref,2),MID(cell_ref,3,2),RIGHT(cell_ref,2))

[aaraneta_splunk]

@jayakumar89 - Did the answer provided by DalJeanis help provide a working solution to your question? If yes, please don't forget to resolve this post by clicking "Accept". If no, please leave a comment with more feedback. Thanks!

[DalJeanis]

Here's an example of something you can do in search results. This code assumes that your results are actually in text format and don't have underlying date data. The first three lines just create sample data, and the rest manipulate it and show you what the result was.

| makeresults | eval MyTimeT="93000"
| append [| makeresults | eval MyTimeT="1500" ]
| append [| makeresults | eval MyTimeT="212530" ]
| eval MyTimeE=strptime(substr("00000".MyTimeT,len(MyTimeT),6),"%H%M%S")
| eval MyTimeF=strftime(MyTimeE,"%c")
| eval MyTimeH=strftime(MyTimeE,"%H:%M:%S")
| table _time MyTimeT MyTimeE MyTimeF MyTimeH

Unfortunately, the result shows that strptime() seems to assume that any time extracted is that time TODAY. Meanwhile, it gives you a start on how you might be able to code that.