topic Re: chart by date ignoring year in Splunk Search

chart by date ignoring year

jyates76 — Wed, 03 Jan 2024 17:00:47 GMT

Im using the search below and basically want a chart showing last 12 dates going oldest to newest from left to right by date.

The search was working fine up until January and year change, now the search only shows the last date in December and is missing the newest 01/02/2024 date. If I change the limit to be large enough to include all date entries in the csv file, I discovered the below:

Its putting the 01/02/2024 date before the oldest date in the csv, instead of putting that 01/02/2024 column after the 12/18/2023 date column. So its like its ignoring the year and going by month chronologically. Done quite a bit of searching on this to no avail, and seems like this should be an easy thing to do... Im not opposed to not using "chart" if someone has a better way. Ideally the search returns the last 12 dates from oldest to newest in the columns and then the team name and numbers sats on that date in the rows. Thansk for any suggestions!

Re: chart by date ignoring year

madhav_dholakia — Wed, 03 Jan 2024 17:08:11 GMT

Hi, is it possible to change the date format to YYYY-MM-DD and then something like this?

| chart sum(sats) over team by Date useother=false | sort 13 Date

Thank you.

Re: chart by date ignoring year

dtburrows3 — Wed, 03 Jan 2024 17:11:21 GMT

I think doing something like this should work.

This will first sort the dates while they are in epoch time and then we convert to human readable timestamps. Then, a transpose is used to retain the order of ascending time from left to right in the header.
Screenshot of local example:

Re: chart by date ignoring year

jyates76 — Wed, 03 Jan 2024 20:33:03 GMT

Thanks, this actually is close with some tweaking but I still cant get around the fact that after the transpose, I want it show the latest 12... Transpose 25 for example will get me the first 25 dates left to right and I want the last 12 right to left if that makes sense? I could do Transpose with no integer to show everything, but then that would be an extremely wide table as this data grows over time on a weekly basis we get a new date, and on those dates we are trying to show number of sats per team for all teams on that date.

Re: chart by date ignoring year

dtburrows3 — Wed, 03 Jan 2024 20:48:25 GMT

I think transforming the data in a normal Splunk timechart format then doing a head 12 and then transposing should do what you are asking.

Example output:

Re: chart by date ignoring year

PickleRick — Wed, 03 Jan 2024 21:02:28 GMT

Wait a second. What's the point of doing strptime/strftime over the same value with the same format?

Re: chart by date ignoring year

dtburrows3 — Wed, 03 Jan 2024 21:07:23 GMT

My assumption is that we are stripping off HH:MM:SS from the original value of Date, but we still want the final results to be in a formatted %m/%d/%Y.

Hard to say for sure without seeing the original dataset.

Re: chart by date ignoring year

PickleRick — Wed, 03 Jan 2024 21:10:38 GMT

For that it would be easier to just cut the date after space. Also working with string-formatted timestamps is just asking for trouble.

Re: chart by date ignoring year

jyates76 — Thu, 04 Jan 2024 15:08:23 GMT

close enough.. I got it down to showing it dates and what I needed in correct order but from right to left using the below