Getting Data In

Timestamp to Date Conversion

apautz22
Loves-to-Learn Lots

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.

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

apautz22
Loves-to-Learn Lots

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

0 Karma
Get Updates on the Splunk Community!

Unlock New Opportunities with Splunk Education: Explore Our Latest Courses!

At Splunk Education, we’re dedicated to providing top-tier learning experiences that cater to every skill ...

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...