Count over Time

[BoKu (Boris Kunstleben)]

Hello and a happy new year,

i ingest the Transports Logs from several Exchange Server via Logstash into Elastic.
I'd like to count the TOP 10 Sender Adresses and TOP 10 Recipient Adresses in a given Timerange, like the one i set on a dashboard.
Like this:

For example: i want to know which 10 Senders send the most Mails (and how much) in the last 12 hours (or 24 hour or so on).

Additionally: i'd like to do the same with the overall count of send and recieved mails, but not in total but in the give / set timerange of the dashboard.
(Source: Analysing Exchange (2013) Message Tracking Logs using NXLog & ELK (ElasticSearch, Logstash, Kibana) | Elijah Paul)

Elastic, Kibana and logstash are all updates to the current version 7.16.2.

Any ideas?

Kind regards
Boris

[nickpeihl]

Hi Boris,

Happy New Year to you, too. I think you want to create a separate visualization each for the Top 10 Senders and Top 10 Recipients. You can create these visualizations in Lens by dragging the appropriate field (example: sender_addresss) into the chart. By default it will aggregate the count by the sender_address. Then create another visualization with the recipient_address field. Add both visualizations to a Dashboard and change the Time Picker to the duration you want. The visualizations should update automatically.

The overall count of emails can also be created in Lens, but you may want to change the visualization type to "Metric" rather than "Bar Vertical Stacked".

Let me know if you have any other questions.

[BoKu (Boris Kunstleben)]

Hi Nick,

thanks for your answer. I exactly did that, BUT that doesn't work. To be more specific, when i change the timerange with the Time Picker, lets say from Today to 15m the visualizations are not updated, no matter what time range i set the visualization counts ALL Sender_adresses / Recipient_adresses since start of the ingest, as you can see in the screenshots.

(Please ignore the fact, that there are just TOP 5 Recipients, there is another Problem displaying 10 recipients)

Greets Boris

[Tomo_M (Tomohiro Mitani)]

Hi,
Have you set an appropriate timestamp field when creating the index pattern?
The "Time Picker" works as range query for the timestamp field of the index pattern, which you can check via Edit Visualization > Inspect > Request.

If you want to control the time range of aggregation by "Time Picker", you need to specify that datetime field for the timestamp field of the index pattern.

[BoKu (Boris Kunstleben)]

Hi,

i think, that the pattern is setup correctly

And this the request-view via Inspect:

there is more then one date-time field, biut i believe that just the @timesstamp will be used.

Boris

[Tomo_M (Tomohiro Mitani)]

I suppose not. While it's a bit confusing, "@timestamp" field is not "timestamp field" automaticaly. If you selected "@timesstamp" field as the "Timestamp field" for the index pattern, a clock icon will show up as follows.

Try remake index pattern and select "@timestamp" as a timestamp field.

When you check the last part of the Request of Inspect, you will find the range query and notice which field was selected as a timestamp field of the index pattern.

[BoKu (Boris Kunstleben)]

Hi,

i just recreated the index pattern and the timestamp has the clock-icon

But the visulization still counts EVERYTHING that matches the field sender-address.

If i check the request, the "range" is completly missing, so the timerange is ignored.

{
  "aggs": {
    "0": {
      "terms": {
        "field": "sender-address.keyword",
        "order": {
          "_count": "desc"
        },
        "size": 10
      }
    }
  },
  "size": 0,
  "fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    },
    {
      "field": "date-time",
      "format": "date_time"
    },
    {
      "field": "date-time,client-ip,client-hostname,server-ip,server-hostname,source-context,connector-id,source,event-id,internal-message-id,message-id,network-message-id,recipient-address,recipient-status,total-bytes,recipient-count,related-recipient-address,reference,message-subject,sender-address,return-path,message-info,directionality,tenant-id,original-client-ip,original-server-ip,custom-data,transport-traffic-type,log-id,schema-version",
      "format": "date_time"
    }
  ],
  "script_fields": {},
  "stored_fields": [
    "*"
  ],
  "runtime_mappings": {},
  "_source": {
    "excludes": []
  },
  "query": {
    "bool": {
      "must": [],
      "filter": [],
      "should": [],
      "must_not": []
    }
  }
}

[Tomo_M (Tomohiro Mitani)]

That sounds strange. Sorry, I have no idea.

[BoKu (Boris Kunstleben)]

After recreating the template now the index is completly messed up. Even in Discover the time graph is gone!
I think there is a problem with my logstash and the data ingest. I'll have a look at this now.

Boris

[BoKu (Boris Kunstleben)]

I just fixed the index, and now i have a working timestamp including the clock icon, but the count still doesn't work as intended.

[BoKu (Boris Kunstleben)]

Solved it ... it was indeed the issue with the timestamp, additionally i didn't consider the fact, that i have to recreate the index template for every space not just for the default space ... the visulizations which weren't working resides in a different space.

[nickpeihl]

Hi Boris,

I'm glad you figured it out. Thanks for sharing your solution, too!