Why am I getting an invalid timestamp using append and appendcols in my search?

[HattrickNZ]

I am working with append and appendcols in a search, but getting an invalid timestamp.

My search looks like this, but I am having issues with the visualization of the chart for this part of search 1.

| inputlookup csv.csv .... <search1> | append [ search ... <search2> | appendcols [ search ... <search3> ] ] 

Search 1 gives me:

      _time     field1  field2  field3  field4
1     2013-06-06     1     2       3       4
...
690 2015-04-26   7     8       9      10

Search 2 gives me:

      _time     field2  field4 
691 2015-04-27   72      82     
...

Search 3 gives me:

      _time     field1  field3 
691 2015-04-27   73   83
...

So I append cols on search1 and search2, then I append this to the bottom of search 1
In search 1 I use where Date2 > start to control the start and end dates
In search 2 & 3 I use starttime= 04/27/2015:00:00:00 latest=@d to control the dates

and this gives me something like:

      _time     field1  field2  field3  field4
1     2013-06-06     1     2       3       4
...
690 2015-04-26   7     8       9       10
691 2015-04-27  73    72      83      82
...

The problem is when I put this together and I hover over a series in the chart. From the first chart, the dates show up as undefined [see the pic below].
However, if I run search 1 on its own, it is fine in that the date is defined and there is no issue. Is this a Splunk bug or my issue? Maybe I can combine these searches together better?

Note in the pic below, the date is fine beyond may 2015 (search 2 & 3), but before that it is an invalid timestamp (search1)

[HattrickNZ · Accepted solution]

The issue here was the file was using a lookup that looked like this

_time       field1   field2   field3   field4
2015-04-27     12      2        3       4
...
2016-03-03     54      6         7      8

but I need to do eval _time = strptime(_time,"%Y-%m-%d") |... to get _time into a date object that Splunk would recognize, otherwise Splunk would just treat it as a a string.

| inputlookup file.csv 
| eval _time = strptime(_time,"%Y-%m-%d") 

[ppablo]

Hi @HattrickNZ

By incomplete, did you mean you were having formatting issues, so your table examples weren't showing up correctly? I cleaned up your post as much as possible and referenced what you posted as an answer below, but wasn't sure if that was all the information you were trying to get across.

[HattrickNZ]

tks ppablo, ye very strange, below is the bit that is missing from the end of the question, you should be able to see where it joins the question above "So I append cols...". If you could add it kindly?

BEGIN

So I append cols on search1 and search2, then I append this to the bottom search 1
In search 1 I use MISSING to control the start and end dates
In search 2 & 3 I use MISSING to control the dates

and this gives me something like:

_time       field 1 field 2 field 3 field 4

1 2013-06-06 1 2 3 4
...
690 2015-04-26 7 8 9 10
691 2015-04-27 73 72 83 82
...

The problem is when I put this together and I hover over a series in the chart, from the first chart, the dates show up as undefined[see the pic below].
However if I run searh 1 on its own it is fine in that the date is defined and there is no issue. Is this a splunk bug or my issue? Maybe I can combine these searches together better?

Note in the pic below the date is fine beyond may 2015(search 2 & 3) But before that it is invalid timestamp(search1)

END

[HattrickNZ]

finally got it posted in the comments. note that MISSING was where Date2 greater than start and the next MISSING was starttime= 04/27/2015:00:00:00 latest=@d

[ppablo]

cool, glad you were able to get it to appear for me to work with 🙂 the only thing that was still missing was the pic you were referencing?