Getting Data In

Help me parse a date/time field that is in HEX

Path Finder

One of my sourcetypes contains a hex date/time field which looks like this:

2E09050F3132

The format of this is:

  • First 2 chars are the hex representation of the year starting at 1970 (so we need to add 1970 to the result)
  • Second 2 are the month starting at 0 (so we need to add one to the month)
  • The rest are 2 character chunks of hex that need to be converted to decimal for day, hour, minute, and second

I know that I can create my own datetime.xml file for this format and put it in my app, but unfortunately, I haven't found very good information for what to put in that file and how to do the math that this requires.

Any thoughts?

1 Solution

SplunkTrust
SplunkTrust

OK. I could be wrong, but I do not think that anything in datetime.xml or anywhere else will help you calculate these times correctly at index time. However if the logs come in more or less at the same time as these timestamps it's not necessarily the end of the world.

Here is how you can calculate the exact times at search time. First here is a toy "stats count" search that you can run locally, dissect and play with, to see how I'm blowing apart the pieces and converting them.

| stats count | eval time="2E09050F3132" | eval hexYear=substr(time,1,2) | eval hexMonth=substr(time,3,2) | eval hexDay=substr(time,5,2) | eval hexHour=substr(time,7,2) | eval hexMinute=substr(time,9,2) | eval hexSecond=substr(time,11,2) | eval strtime=tostring(1970 + tonumber(hexYear,16)) +  "/" + tostring(tonumber(hexMonth,16)+1) + "/" + tostring(tonumber(hexDay,16)) + " " + tostring(tonumber(hexHour,16)) + ":" + tostring(tonumber(hexMinute,16)) + ":" + tostring(tonumber(hexSecond,16)) | eval epochtime=strptime(strtime,"%Y/%m/%d %H:%M:%S") | eval _time=epochtime

As far as running it on your actual data, if the field is called "time", it would look like so:

<your search terms> | eval hexYear=substr(time,1,2) | eval hexMonth=substr(time,3,2) | eval hexDay=substr(time,5,2) | eval hexHour=substr(time,7,2) | eval hexMinute=substr(time,9,2) | eval hexSecond=substr(time,11,2) | eval strtime=tostring(1970 + tonumber(hexYear,16)) +  "/" + tostring(tonumber(hexMonth,16)+1) + "/" + tostring(tonumber(hexDay,16)) + " " + tostring(tonumber(hexHour,16)) + ":" + tostring(tonumber(hexMinute,16)) + ":" + tostring(tonumber(hexSecond,16)) | eval _time=strptime(strtime,"%Y/%m/%d %H:%M:%S") | timechart count

and I just stuck a timechart count on the end to show how at that point timechart and other commands that operate on _time, will happily chew on these time values we've created instead of the other time they started with.

Granted, if the events are being retrieved with the _time values of the server at the moment it was indexed, and these drift slightly forward or back from these hextime values, your reporting will be necessarily a little fuzzy and strange at the edges. You can refilter to screen out hexTimes that were matched into your timerange that weren't supposed to be, but you can't conjure up rows that were never matched in the base search clause to begin with. One option is to make the timerange deliberately a little too big, and then clip it down. ymmv.

Other crazy ideas
1 have a scripted input read the files (ick), fix the times in python (ick), and manually do all the stuff that the data inputs normally do (rolling, weird write/lock scenarios (yuck))
2 index the data like this into index=A, then create a scheduled search with the collect command to process the times the way you want and "summary" index them into index=B. I put summary in quotes because when you use the collect command manually, technically you don't have to actually have a transforming command, so here we'd be just putting exact copies of the events into index=B, the only difference being the repaired times. Kinda ick though admittedly.
3 Miracle occurs, and somehow someone else knows a way whereby you can handle this with datetime.xml and props.

View solution in original post

SplunkTrust
SplunkTrust

OK. I could be wrong, but I do not think that anything in datetime.xml or anywhere else will help you calculate these times correctly at index time. However if the logs come in more or less at the same time as these timestamps it's not necessarily the end of the world.

Here is how you can calculate the exact times at search time. First here is a toy "stats count" search that you can run locally, dissect and play with, to see how I'm blowing apart the pieces and converting them.

| stats count | eval time="2E09050F3132" | eval hexYear=substr(time,1,2) | eval hexMonth=substr(time,3,2) | eval hexDay=substr(time,5,2) | eval hexHour=substr(time,7,2) | eval hexMinute=substr(time,9,2) | eval hexSecond=substr(time,11,2) | eval strtime=tostring(1970 + tonumber(hexYear,16)) +  "/" + tostring(tonumber(hexMonth,16)+1) + "/" + tostring(tonumber(hexDay,16)) + " " + tostring(tonumber(hexHour,16)) + ":" + tostring(tonumber(hexMinute,16)) + ":" + tostring(tonumber(hexSecond,16)) | eval epochtime=strptime(strtime,"%Y/%m/%d %H:%M:%S") | eval _time=epochtime

As far as running it on your actual data, if the field is called "time", it would look like so:

<your search terms> | eval hexYear=substr(time,1,2) | eval hexMonth=substr(time,3,2) | eval hexDay=substr(time,5,2) | eval hexHour=substr(time,7,2) | eval hexMinute=substr(time,9,2) | eval hexSecond=substr(time,11,2) | eval strtime=tostring(1970 + tonumber(hexYear,16)) +  "/" + tostring(tonumber(hexMonth,16)+1) + "/" + tostring(tonumber(hexDay,16)) + " " + tostring(tonumber(hexHour,16)) + ":" + tostring(tonumber(hexMinute,16)) + ":" + tostring(tonumber(hexSecond,16)) | eval _time=strptime(strtime,"%Y/%m/%d %H:%M:%S") | timechart count

and I just stuck a timechart count on the end to show how at that point timechart and other commands that operate on _time, will happily chew on these time values we've created instead of the other time they started with.

Granted, if the events are being retrieved with the _time values of the server at the moment it was indexed, and these drift slightly forward or back from these hextime values, your reporting will be necessarily a little fuzzy and strange at the edges. You can refilter to screen out hexTimes that were matched into your timerange that weren't supposed to be, but you can't conjure up rows that were never matched in the base search clause to begin with. One option is to make the timerange deliberately a little too big, and then clip it down. ymmv.

Other crazy ideas
1 have a scripted input read the files (ick), fix the times in python (ick), and manually do all the stuff that the data inputs normally do (rolling, weird write/lock scenarios (yuck))
2 index the data like this into index=A, then create a scheduled search with the collect command to process the times the way you want and "summary" index them into index=B. I put summary in quotes because when you use the collect command manually, technically you don't have to actually have a transforming command, so here we'd be just putting exact copies of the events into index=B, the only difference being the repaired times. Kinda ick though admittedly.
3 Miracle occurs, and somehow someone else knows a way whereby you can handle this with datetime.xml and props.

View solution in original post

Splunk Employee
Splunk Employee

As always, brilliant answers from sideview!

0 Karma

Path Finder

Thanks for the really quick reply. I saw those in my Google search. My problem is that both of those cases appear to use hexepoch which I think means that the structure of the date field is the number of seconds since epoch and not broken out like I need.

It isn't hard to write a datetime.xml file. But the problem I have is what time patterns exist in Splunk (and are they documented anywhere)? For example:

          <timePatterns>
            <use name="_hexepoch"/>
          </timePatterns>

Is there also a _hexyear, _hexmonth, etc? Does _hexyear add 1970 to the year? If not, how do I do that?

Thanks!

0 Karma