Find latest value of X for each term in Y - Splunk equivalent

[wiouser (Veera)]

Hi,

I have a time series data in an index.
I am trying to find latest value of a field1 split by terms in field2.
Then apply a filter example field1=SUCCESS and count the resulting terms.
I just want to return only the final count.

I am from splunk background and exploring elasticsearch. In splunk I do
index | stats latest(field1) as latest_status by field2 | where latest_status="SUCCESS" | stats count

Is there a equivalent for this in elasticsearch. Please help.

[katara]

How are you pushing this data to ES?
If its with Logstash,
You can try this in your elasticsearch output.
If your input is JDBC for example,
Assuming your field2 is a time field,

input {
  jdbc {
    ...
    statement => "SELECT field1,field2 from yourtable where field2 > :sql_last_value"
    use_column_value =>true
        tracking_column =>field2
        tracking_column_type => "timestamp"
    last_run_metadata_path => "/Users/me/.logstash_jdbc_last_run" 
    ...
  }
}
output {
  elasticsearch {
    hosts => ["yourelasticIP"]
    index => "yourindex"
    action=>update
    document_id => "%{field1}"
    doc_as_upsert =>true
}
        stdout { codec => rubydebug }
}

Katara.

[wiouser (Veera)]

Thanks for the reply. Field2 is not time. This is how my documents look like

{time, field1, field2}
{03:00, T1, failure}
{03:00, T1, success}
{02:00, T1, failure}
{02:00, T2, success}
{01:00, T2, failure}

I want to find the latest status for every term in field1 and then count the number of success.

And it's not logstash and we cannot update existing documents for new status documents.

Thank you

[katara]

Okay, How are you pushing the data to Es from your source?

[wiouser (Veera)]

Through the index api.
Whenever the validate function is called from client, the server processes it sends response back to client and logs in elasticsearch index through the rest api.

[katara]

Okay,
You have to have _timestamp enabled in ES.
and then you can query the latest data.

{
  "query": {
    "match_all": {}
  },
  "size": 1,
  "sort": [
    {
      "_timestamp": {
        "order": "desc"
      }
    }
  ]
}

Is this something you are looking for?