# Adding new fields from timestamp nvarchar(max) with grok filter in logstash

**URL:** <https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645>\
**Category:** Logstash\
**Tags:** kql-kibana-query-language\
**Created:** [January 21, 2023, 10:27am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645 "2023-01-21T10:27:52Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dor\_Steinberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dor_steinberg/32/116235_2.png) [@Dor\_Steinberg](https://discuss.elastic.co/u/Dor_Steinberg)\
**Post date:** [January 21, 2023, 10:27am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/1 "2023-01-21T10:27:52Z")

</div>

## hey everyone, i want to create 2 new fields (as month, year) from an existing timestamp field that comes from a json filter (payload) when the timestamp field type is nvarchar(max) I tried going in several directions including filter grok or creating an index template for the specific index (I did not try through KQL) I would love to know which approach you think should be followed here is an example of the conf:

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:sqlserver://10.211.2.9:1433;databaseName=SNJ\_CAP\_CBM;integratedSecurity=false;encrypt=false;trustServerCertificate=true;"  
jdbc\_user =\> "SO"  
jdbc\_password =\> "1234"  
jdbc\_driver\_library =\> "/etc/logstash/conf.d/logstash-core/lib/jars/mssql-jdbc-11.2.0.jre8.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
statement =\> "select payload from dbo.tbl\_partly\_log"  
}  
}  
filter {

json {  
source =\> "payload"  
}  
}  
output {  
elasticsearch {  
hosts =\> ["[http://10.211.3.164:9200](http://10.211.3.164:9200)"]  
index =\> "cbm"  
doc\_as\_upsert =\> true

```
}

```

## }

It is important to note that the logs are taken from the SQL SERVER DB server from which I take JSON and filter its fields to independent  
Thanks for the helpers 🙂

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [January 21, 2023, 10:48am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/2 "2023-01-21T10:48:25Z")

</div>

Welcome to the community  
Can you show how data looks like? I mean from where you need to extract, past a sample.

---

<div class="post-metadata">

**Author:** ![Dor\_Steinberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dor_steinberg/32/116235_2.png) [@Dor\_Steinberg](https://discuss.elastic.co/u/Dor_Steinberg)\
**Post date:** [January 21, 2023, 11:01am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/3 "2023-01-21T11:01:01Z")

</div>

here is example of the log:

{"log": {"process\_guid": "0042CF45-A073-4E19-9352-60A0609CB12E", "operation\_type": "act", "timestamp": "2020-08-23 14:07:56.264521", "source\_system": "crm", "target\_system": "magam", "process\_type": "addfile", "process\_type\_heb": "הוספת קובץ", "sub\_process": "", "sub\_process\_heb": "", "app\_version": "", "machine\_name": "PARTLY", "status": "OK", "severity": "INFO", "payload": {"header": {"process\_type": "addfile", "user\_identifier": "guy"}, "payload": {"research\_details": {"research\_id": 10077}, "files": [{"zip\_file\_name": "be559412-95be-11ed-aafe-02ca.zip"}, {"zip\_file\_name": "be559413-95be-11ed-92d9-02ca47.zip"}, {"zip\_file\_name": "be559414-95be-11ed-8dd3-02ca47.zip"}]}}}}

so you can see that i am using the timestamp in log

---

<div class="post-metadata">

**Author:** ![Dor\_Steinberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dor_steinberg/32/116235_2.png) [@Dor\_Steinberg](https://discuss.elastic.co/u/Dor_Steinberg)\
**Post date:** [January 21, 2023, 11:08am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/4 "2023-01-21T11:08:49Z")

</div>

> [@Dor\_Steinberg](#):
>
> payload

this is example from the select statement (payload - json field) in the input

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [January 21, 2023, 11:54am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/5 "2023-01-21T11:54:32Z")

</div>

> [@Dor\_Steinberg](#):
>
> timestamp": "2020-08-23 14:07:56.264521"

```auto
	grok {
		match => { "timestamp" => "%{YEAR:year}-%{MONTHNUM:month}-%{MONTHDAY}%{GREEDYDATA}" }
	}

```

Result:

```auto
{
          "month" => "08",
           "year" => "2020"
}

```

You should also convert to date field:

```auto
     date {
     match => ["timestamp", "YYYY-MM-dd HH:mm:ss.SSSSSS"]
     target => "timestamp"
    }

```

PS Don't forget to recreate index pattern.

---

<div class="post-metadata">

**Author:** ![Dor\_Steinberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dor_steinberg/32/116235_2.png) [@Dor\_Steinberg](https://discuss.elastic.co/u/Dor_Steinberg)\
**Post date:** [January 21, 2023, 12:05pm UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/6 "2023-01-21T12:05:47Z")

</div>

> [@Rios](#):
>
> ```auto
> date {
> match => ["timestamp", "YYYY-MM-dd HH:mm:ss.SSSSSS"]
> target => "timestamp"
> }
> 
> ```

i added your change's:  
as you can see below

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:sqlserver://10.211.2.9:1433;databaseName=SNJ\_CAP\_CBM;integratedSecurity=false;encrypt=false;trustServerCertificate=true;"  
jdbc\_user =\> "SO"  
jdbc\_password =\> "1234"  
jdbc\_driver\_library =\> "/etc/logstash/conf.d/logstash-core/lib/jars/mssql-jdbc-11.2.0.jre8.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
statement =\> "select payload from dbo.tbl\_partly\_log"  
}  
}  
filter {

json {  
source =\> "payload"  
}  
grok {  
match =\> { "timestamp" =\> "%{YEAR:year}-%{MONTHNUM:month}-%{MONTHDAY}%{GREEDYDATA}" }  
}  
date {  
match =\> ["timestamp", "YYYY-MM-dd HH:mm:ss.SSSSSS"]  
target =\> "timestamp"  
}  
}  
output {  
elasticsearch {  
hosts =\> ["[http://10.211.3.164:9200](http://10.211.3.164:9200)"]  
index =\> "cbm"  
doc\_as\_upsert =\> true

```
}

```

}

can you tell me if the syntax is ok?

i will update soon if it works 🙂  
thanks

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [January 21, 2023, 12:09pm UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/7 "2023-01-21T12:09:41Z")

</div>

Yes this is it.

Optionally: You can add in output to see your data how will look like before insert.

```auto
output {
    stdout {
        codec => rubydebug{}
     }
    elasticsearch {...
    }
}

```

---

<div class="post-metadata">

**Author:** ![Dor\_Steinberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dor_steinberg/32/116235_2.png) [@Dor\_Steinberg](https://discuss.elastic.co/u/Dor_Steinberg)\
**Post date:** [January 21, 2023, 12:15pm UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/8 "2023-01-21T12:15:05Z")

</div>

> [@Rios](#):
>
> ```auto
> stdout {
> codec => rubydebug{}
> 
> ```

appreciate the help

---

<div class="post-metadata">

**Author:** ![Dor\_Steinberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dor_steinberg/32/116235_2.png) [@Dor\_Steinberg](https://discuss.elastic.co/u/Dor_Steinberg)\
**Post date:** [January 22, 2023, 8:34am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/9 "2023-01-22T08:34:01Z")

</div>

when i run the conf file with the changes, its creating an index, but there is no year, month fields

as you can see the mapping of the index (not include those fields)

{  
"mappings": {  
"properties": {  
"@timestamp": {  
"type": "date"  
},  
"@version": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"log": {  
"properties": {  
"app\_version": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"machine\_name": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"operation\_type": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"payload": {  
"properties": {  
"header": {  
"properties": {  
"process\_type": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"user\_identifier": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
}  
}  
},  
"payload": {  
"properties": {  
"files": {  
"properties": {  
"zip\_file\_name": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
}  
}  
},  
"research\_details": {  
"properties": {  
"research\_id": {  
"type": "long"  
},  
"research\_name\_heb": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
}  
}  
},  
"software": {  
"properties": {  
"software\_component": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"software\_version": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
}  
}  
},  
"users": {  
"properties": {  
"email": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"first\_name": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"first\_name\_heb": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"id": {  
"type": "long"  
},  
"last\_name": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"last\_name\_heb": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"phone": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
}  
}  
}  
}  
}  
}  
},  
"process\_guid": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"process\_type": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"process\_type\_heb": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"severity": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"source\_system": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"status": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"sub\_process": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"sub\_process\_heb": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"target\_system": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"timestamp": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
}  
}  
},  
"payload": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"tags": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
}  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![Dor\_Steinberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dor_steinberg/32/116235_2.png) [@Dor\_Steinberg](https://discuss.elastic.co/u/Dor_Steinberg)\
**Post date:** [January 22, 2023, 9:17am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/10 "2023-01-22T09:17:57Z")

</div>

it seems log.timestamp not converted to date type

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [January 22, 2023, 9:23am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/11 "2023-01-22T09:23:19Z")

</div>

Change index =\> "cbm" to index =\> "test" or whatever you want, reload data, create index pattern. Should work.

---

<div class="post-metadata">

**Author:** ![Dor\_Steinberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dor_steinberg/32/116235_2.png) [@Dor\_Steinberg](https://discuss.elastic.co/u/Dor_Steinberg)\
**Post date:** [January 22, 2023, 9:28am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/12 "2023-01-22T09:28:07Z")

</div>

## i found the solution, beacuse the timestamp is in json nested format, i had to get into the log.timestamp in that way

grok {  
match =\> { "%{[log][timestamp]}" =\> "%{YEAR:year}-%{MONTHNUM:month}-%{MONTHDAY}%{GREEDYDATA}" }  
}  
date {  
match =\> ["%{[log][timestamp]}", "YYYY-MM-dd HH:mm:ss.SSSSSS"]  
target =\> %{"[log][timestamp]}"  
}  
}

thank for the help  
its working:)

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [January 22, 2023, 9:57am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/13 "2023-01-22T09:57:40Z")

</div>

Good catch, well done. 👍 I have focused only on the time field. 🙃

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 19, 2023, 9:58am UTC](https://discuss.elastic.co/t/adding-new-fields-from-timestamp-nvarchar-max-with-grok-filter-in-logstash/323645/14 "2023-02-19T09:58:22Z")

</div>

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