Hi everyone!
I have the following setup: filebeat (collects log file) > logdash (filters JSON) > elasticsearch > kibana.
In kibana, I mapped the data as follows:
get filebeat-2017*/_mapping/log results in:
.
.
.
"commandCommitScn": {
"type": "long",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"commandScn": {
"type": "long",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"commandSequence": {
"type": "long",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"commandTimestamp": {
"type": "date",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"commandType": {
"type": "keyword",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"error": {
"type": "keyword",
"ignore_above": 1024
},
"fieldChanged": {
"type": "keyword",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"fieldId": {
"type": "keyword",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"fieldType": {
"type": "keyword",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"fieldValue": {
"type": "keyword",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"fileset": {
"properties": {
"module": {
"type": "keyword",
"ignore_above": 1024
},
"name": {
"type": "keyword",
"ignore_above": 1024
}
}
...
as you can see, the terms I want to aggragate by are defined as keywords in the mapping.
When I use Discover in kibana, the message field is:
...
"fieldType": "VARCHAR2",
"fieldValue": "85928 CEDEX 9"
"fieldType": "DATE",
...
So it seems like kibana understands that this terms are not plain text, but a terms that need to be read by themselves.
The problem is that I can't find an option in the kibana aggregation in Visualize, that will anable me to aggregate by this fields (more specificly - by there results, for example: how many "fieldType" are dates and how many are other types).
How can I aggregate by the keywords I want?
This is the logstash confic I use:
input {
beats {
port => 5044
}
}
filter{
split{
field => "changedFieldList"
}
json{
source => "message"
target => "parsedJson"
}
}
output {
elasticsearch {
hosts => ["http://127.0.0.1:9200"]
index => "%{[@metadata][beat]}-%{+YYYY.MM.dd}"
document_type => "%{[@metadata][type]}"
}
}
And the file I insert is something like this:
{
"ChangeInfo": "Archive Log Set archiveLogSet.108058603.108058399 Info:Thread# Sequence# FirstScn LastScn ",
"documentsList": [{
"commandScn": "108058599",
"commandCommitScn": "108058600",
"commandSequence": "0",
"commandType": "UPDATE",
"commandTimestamp": "2017-08-22 14:37:53+03:000",
"objectDBName": "DEV2",
"objectSchemaName": "YISHAIN",
"objectId": "CUSTOMERS",
"changedFieldsList": [{
"fieldId": "CUSTOMER_ID",
"fieldType": "NUMBER",
"fieldValue": "17",
"fieldChanged": "N"
}, {
"fieldId": "GENDER",
"fieldType": "VARCHAR2",
"fieldValue": "Male",
"fieldChanged": "N"
}, {
"fieldId": "DATE_OF_BIRTH",
"fieldType": "DATE",
"fieldValue": "1968-04-01 00:00:00+03:000",
"fieldChanged": "N"
}, {
"fieldId": "CREDIT_LIMIT",
"fieldType": "NUMBER",
"fieldValue": "14161",
"fieldChanged": "N"
}, {
"fieldId": "INCOME_LEVEL",
"fieldType": "NUMBER",
"fieldValue": "67035",
"fieldChanged": "N"
}, {
"fieldId": "COMMENTS",
"fieldType": "VARCHAR2",
"fieldValue": "stuff",
"fieldChanged": "N"
}, {
"fieldId": "LAST_UPDTAE",
"fieldType": "DATE",
"fieldValue": "2016-01-12 00:00:00+03:000",
"fieldChanged": "N"
}],
"conditionFieldsList": [{
"fieldId": "CUSTOMER_ID",
"fieldType": "NUMBER",
"fieldValue": "17"
}, {
"fieldId": "CUSTOMER_FIRST_NAME",
"fieldType": "VARCHAR2",
"fieldValue": "Daniel"
}, {
"fieldId": "CUSTOMER_LAST_NAME",
"fieldType": "VARCHAR2",
"fieldValue": "Washington"
}, {
"fieldId": "CUSTOMER_COUNTRY",
"fieldType": "VARCHAR2",
"fieldValue": "France"
}, {
"fieldId": "CUSTOMER_CITY",
"fieldType": "VARCHAR2",
"fieldValue": "La Roche-sur-Yon"
}, {
"fieldId": "CUSTOMER_STREET",
"fieldType": "VARCHAR2",
"fieldValue": "5 Maple Pass"
}, {
"fieldId": "CUSTOMER_ZIPCODE",
"fieldType": "VARCHAR2",
"fieldValue": "85928 CEDEX 9"
}, {
"fieldId": "PHONE_NUMBER",
"fieldType": "VARCHAR2",
"fieldValue": "33-(414)618-3273"
}, {
"fieldId": "EMAIL",
"fieldType": "VARCHAR2",
"fieldValue": "dwashingtong@tiny.cc"
}, {
"fieldId": "GENDER",
"fieldType": "VARCHAR2",
"fieldValue": "Male"
}, {
"fieldId": "DATE_OF_BIRTH",
"fieldType": "DATE",
"fieldValue": "1968-04-01 00:00:00+03:000"
}, {
"fieldId": "CREDIT_LIMIT",
"fieldType": "NUMBER",
"fieldValue": "14161"
}, {
"fieldId": "INCOME_LEVEL",
"fieldType": "NUMBER",
"fieldValue": "67035"
}, {
"fieldId": "COMMENTS",
"fieldType": "VARCHAR2",
"fieldValue": "stuff"
}, {
"fieldId": "LAST_UPDTAE",
"fieldType": "DATE",
"fieldValue": "2016-01-12 00:00:00+03:000"
}]
}]
}
What do I need to do in order to aggregate and visualize by the terms in the JSON file?
Thanks!
