inconsistency in hourly vs daily aggregation

[priamai]

Hi there,
sorry to bother you but I noticed something weird with the aggregation.

from pandagg.discovery import discover
from pandagg import Search

indices = discover(es_client, "filebeat-*")
filebeat = indices['filebeat-7.14.0-2021.08.24-000001']
search = filebeat.search().filter().size(1).groupby('alerts_day', 'date_histogram', fixed_interval='1d',field='@timestamp',format="yyyy-MM-dd")

response = search.execute()
day_df = response.aggregations.to_dataframe()
day_df.reset_index(inplace=True)
day_df.index = pd.to_datetime(day_df['alerts_day'])
day_df.drop(['alerts_day'], axis=1,inplace=True)

search = filebeat.search().size(1).groupby('alerts_day', 'date_histogram', fixed_interval='1h', field='@timestamp',format="yyyy-MM-dd hh:mm:ss")
response = search.execute()
hour_df = response.aggregations.to_dataframe()
hour_df.reset_index(inplace=True)
hour_df.index = pd.to_datetime(hour_df['alerts_day'])
hour_df.drop(['alerts_day'], axis=1,inplace=True)

That is all fine then I do:

day_df.doc_count.sum()
hour_df.doc_count.sum()

This produces 7775 vs 1722 documents, the first number is correct the latter is not.
I am sure there is some detail I am missing but can't figure it out!

[alk-lbinet]

Hi @priamai ,
In order to detect if issues is from pandagg I would need you to provide:

• the search query search.to_dict(): to check if there is an error while building the search query
• the response aggregation data response.aggregations.data: to check whether there is an error while parsing aggregation result in tabular format

thx

[robomotic]

Yes one sec checking!

[robomotic]

The first query search to dict:

{'query': {'bool': {'filter': [{'term': {'log.file.path': {'value': 'first-org-conf-2015-eve.json'}}}]}}, 'aggs': {'alerts_day': {'date_histogram': {'field': '@timestamp', 'format': 'yyyy-MM-dd', 'fixed_interval': '1d'}}}, 'size': 1}

The second query search to dict:

{'query': {'bool': {'filter': [{'term': {'log.file.path': {'value': 'first-org-conf-2015-eve.json'}}}]}}, 'aggs': {'alerts_day': {'date_histogram': {'field': '@timestamp', 'format': 'yyyy-MM-dd hh:mm:ss', 'fixed_interval': '1h'}}}, 'size': 1}

[robomotic]

The second query data frame:

Raw shape:

raw.shape
(476, 1)

[robomotic]

One important detail is that those timestamps are in UTC, I am wondering if that is somehow interfering with the conversion?

[cwhaley8288]

I realize this is older, but had a similar issue, I think its syntax of "hour" - this appears to fix it

.groupby('stat_hour', 'date_histogram', fixed_interval='1H',field='@timestamp',format="yyyy-MM-dd HH:mm:ss")\

[priamai]

You are bang on, I am pretty sure is the format indeed. I will test it and report it here.