Search filter for only specific hours in a month


(kav) #1

Hi everyone,

I have a SIEM use case that require to do a search for After Office hour login and generate a 1 month report with this filter.

Any advice on how could i filter the hour from @timestamp (or any better approach?)

I got a successful way to do it by adding a new field named hourofday using painless with script below

doc['@timestamp'].date.hourofDay

However this might not be the approach my management would prefer as they are afraid that by adding field into the log might abuse the Integrity of logs.

Would appreciate if anyone could shed some lights on:

  • Is it possible to filter Hour of document so that i could generate a report for after office hour (assume office hour = 9AM to 6PM), and if yes, how could I achieve so?

Also, just a quick query, will the raw logs be modified when I uses painless script to add a field into documents? As we are not suppose to change anything to raw logs due to the C.I.A rules.

Thanks
Regards


(Alexander Reelsen) #2

Hey,

a couple of statements here:

  1. a painless script will never modify your data.
  2. Using a script means that for every search hit of a query this script needs to be executed. If you have millions of hits, you will have millions of those executions, so think if there is another way of doing this to keep your queries fast
  3. From what I read you might not need the hour of the day, you can just use a bunch of filters. If you search for the last 7 days, you would need five filters that exclude office hours, instead of checking for the hour of the day.
  4. If you want to speed this up tremendously and you want to be able to really search for the hour of the day, you should rethink your strategy and perhaps just index the hour of the day as a dedicated field, as this will make it very easy to write a query.

Hope this helps!

--Alex


(kav) #3

Hi Alex,

Thanks for the informative and helpful response,

just to confirm again with you, indexing a new dedicate field will not make any changes in the Raw log, Am I right?

As long as the above statement is true, then it should be fine to use the index approach.

Thanks
Kavier


(Alexander Reelsen) #4

to be honest I do not understand the question. Are you referring to the field, that stores the original message? Adding a new field will simply add some more data to each document that gets indexed, but not modify anything else.


(kav) #5

Hi Alex,

Apologize for the confusion,
see below example for a better view

Architecture:
Window device > Beat to collect winlogs > output to ElasticSearch

Questions:

  1. Will Elasticsearch store a copy of raw logs that was sent by winlogbeats (that are not modified nor parsed)?
  2. For Adding new fields will add more data to each document that gets indexed, will adding a new field changes the Question 1's raw logs (eg: add a new line name hourofday=8 at the end of logs)?

The objective is to make sure that there will be a copy of log from Source devices that remained at its raw format as any enriched, or normalized logs will not able to pass the compliance.

Hence we are concern about adding a new field could change the raw logs.

Really appreciate your time and effort on assisting with my query!

Thanks!

Kavier