Matching string field and changing it to date

Elastic Stack Logstash
1

Hi everyone!
I have the following JSON doc:

{
"DataChangeInfo" : "Archive Log Set archiveLogSet.25933761.25933688",
"documentsList" : [
{
  "commandScn": "25933758",
  "commandCommitScn": "0",
  "commandSequence": "3",
  "commandType": "INSERT",
  "commandTimestamp": "2017-12-07 05:09:54+03:000",
  "objectDBName": "DB4",
  "objectSchemaName": "CFTNA",
  "objectId": "NEWJOU",
  "changedFieldsList": [
    {
      "fieldId": "PK_NEWJOU",
      "fieldType": "NUMBER",
      "fieldValue": "NULL",
      "fieldChanged": "Y"
    },
    {
      "fieldId": "OFFICE",
      "fieldType": "CHAR",
      "fieldValue": "NULL",
      "fieldChanged": "Y"
    },
    {
      "fieldId": "UPDATE_DATE",
      "fieldType": "DATE",
      "fieldValue": "2017-12-07 05:09:54+03:000",
      "fieldChanged": "Y"
    },
    {
      "fieldId": "ENDDATE",
      "fieldType": "DATE",
      "fieldValue": "NULL",
      "fieldChanged": "Y"
}
]
}

Now, "commandTimestamp": "2017-12-07 05:09:54+03:000" is the relevant field that I want to be saved as a date, and currently it's being saved as a string.

How can I do this?
It's not necessarily the field I want to be saved as @timestamp, but I want to aggregate by time in this field, and not as string.

I tried the following filter in logstash:

    date{
            match => ["[documentsList][commandTimeStamp]","yyyy-MM-dd HH:mm:ss","ISO8601","yyy-MM-dd HH:mm:ssZZZ","UNIX"]
            target => ["new_timestamp"]
    }

and I don't see any results or any new fields.
What am I doing wrong?
How can I do this?
Thanks!

2

The documentsList field is an array. You can use [documentsList][0][commandTimeStamp] to address its first element and the commandTimeStamp field within it.

You have a weird time format so I wouldn't be surprised if your date filter can't parse it. What's up with the final zero in the timezone offset? If it's always zero at the end you could set the pattern to expect a zero there; yyyy-MM-dd HH:mm:ssZZ'0' will probably work.

1 Like
3

Hi, thank you for the answer.
I did as you suggested:

date{
                match => ["[documentsList][0][commandTimeStamp]","yyyy-MM-dd HH:mm:ssZZ'0'","ISO8601","yyy-MM-dd HH:mm:ssZZZ","UNIX"]
                target => ["new_timestamp"]
        }

but still, when I check the mapping for my index I see:

"commandTimestamp": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              }

And I see no new field "new_timestamp" the way I defined in the "target" line in the filter.
What am I doing wrong?
BTW, there are no error messages in logstash error through the process.

4

sorry, my bad. your solution solved it, there was a typo.
Thank you very much!

5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.