Hi everyone!
I'm trying to get some useful stats on my logged data. I have 3 attributes in each log entry, HARVEST_DATE, PUBLISH_DATE and MEDIA_TYPE. I would like to calculate the average difference between PUBLISH_DATE and HARVEST_DATE, as a timestamp (miliseconds).
To do so, I'm executing the following command:
index=sfmc-ris TYPE=PUSH_POST_ASYNC SUCCESS=true
| eval PUBLISH_DATE_TIMESTAMP=strptime(PUBLISH_DATE, "%Y-%m-%d %H:%M:%S,%3Q")
| eval HARVEST_DATE_TIMESTAMP=strptime(HARVEST_DATE, "%Y-%m-%d %H:%M:%S,%3Q")
| eval HARVEST_TO_PUBLISH=round(HARVEST_DATE_TIMESTAMP-PUBLISH_DATE_TIMESTAMP, 1)
| stats avg(HARVEST_TO_PUBLISH) as HARVEST_PUBLISH_AVG by MEDIA_TYPE
However, that outputs no results at all for HARVEST_PUBLISH_AVG. If I remove the by MEDIA_TYPE part, it will show me the average difference without any grouping, no issues.
Can anyone help me figure out what am I doing wrong?
Thanks!
... View more