Splunk Search

chart by date ignoring year

jyates76
Explorer

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

| inputlookup running_data.csv
| eval EP=strptime(Date, "%m/%d/%Y")
| eval Date=strftime(EP, "%m/%d/%Y")
| chart sum(sats) over team by Date useother=false limit=12
| fillnull value=0

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:
splunk chart by date example.png

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!

Labels (1)
Tags (3)
0 Karma
1 Solution

dtburrows3
Builder

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.

 

 

| inputlookup running_data.csv
    | eval
        _time=strptime(Date, "%m/%d/%Y")
    | sort 0 -_time
    | timechart span=1d
        sum(sats) as sats
            by team
    | head 12
    | eval
        Date=strftime(_time, "%m/%d/%Y")
    | fields - _*
    | transpose 12 header_field=Date
    | rename
        column as team

 

 

Example output:

dtburrows3_0-1704314762608.png

 

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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

0 Karma

dtburrows3
Builder

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.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

0 Karma

dtburrows3
Builder

I think doing something like this should work.

 

| inputlookup running_data.csv
    | eval
        EP=strptime(Date, "%m/%d/%Y")
    | chart
        sum(sats) as sats
            over EP
            by team
    | sort 0 +EP
    | eval
        Date=strftime(EP, "%m/%d/%Y")
    | fields - EP
    | transpose 25 header_field=Date
    | rename
        column as team

 

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:

dtburrows3_0-1704301832167.png

 

jyates76
Explorer

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.

0 Karma

dtburrows3
Builder

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.

 

 

| inputlookup running_data.csv
    | eval
        _time=strptime(Date, "%m/%d/%Y")
    | sort 0 -_time
    | timechart span=1d
        sum(sats) as sats
            by team
    | head 12
    | eval
        Date=strftime(_time, "%m/%d/%Y")
    | fields - _*
    | transpose 12 header_field=Date
    | rename
        column as team

 

 

Example output:

dtburrows3_0-1704314762608.png

 

jyates76
Explorer

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

| inputlookup running_data.csv
| eval _time=strptime(Date, "%m/%d/%Y")
| sort 0 -_time
| timechart span=1d sum(sats) as sats by team useother=false limit=0
| fillnull value=0
| tail 12
| eval Date=strftime(_time, "%m/%d/%Y")
| fields - _*
| transpose 12 header_field=Date
| rename column as team

jyates76_0-1704376606477.png

 

0 Karma

madhav_dholakia
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...