Timestamp to Date Conversion

[apautz22]

I'm having an issue with a dashboard which is reporting UPC counts by day.
If I use the following query, it gives the UPC counts, but not the date

<query>index=web  clientApp=MOBILE enterpriseId=prod locationID=Lima country=US command=item tagName=REMOTE_REQUEST | top 0 showperc=f message.request.payload.upc | rename message.request.payload.upc as "UPC", count as "Count"</query>

If I add the timestamp into it like the following query, then it gives the count for every UPC as 1 because they have a unique timestamp.

<query>index=web  clientApp=MOBILE enterpriseId=prod locationID=Lima country=US command=item tagName=REMOTE_REQUEST | top 0 showperc=f message.request.payload.upc message.request.payload.Time | rename message.request.payload.upc as "UPC", count as "Count"</query>

I have tried every way i can find with eval and strptime and strftime to get it to give me just the year, month, day format, but I can't get it to show that.

Admittedly I'm a noob, but I have searched for a couple days trying to find an answer and can't.

[woodcock · Accepted solution]

Try this:

index=web  clientApp=MOBILE enterpriseId=prod locationID=Lima country=US command=item tagName=REMOTE_REQUEST
| bin _time span=1d
| top 0 showperc=f message.request.payload.upc BY _time
| rename message.request.payload.upc AS UPC count AS Count

Or this:

index=web  clientApp=MOBILE enterpriseId=prod locationID=Lima country=US command=item tagName=REMOTE_REQUEST
| bin _time span=1d
| stats count AS Count BY message.request.payload.upc _time
| rename message.request.payload.upc AS UPC
| sort 0 - Count

[apautz22]

They both worked - thank you very much for the assistance 🙂