Reporting

Sort by date

AnguaSec
Loves-to-Learn Lots

I have updated a csv file and one of the fields is a date.   I need to sort the data by date order then I can visualise a graph with it but it won't sort by date. 

I've read the posts about changing to Epoch  time then sorting or using strftime, etc, but none of them have worked.

I found the answer on how to change the field data to show as a date which worked is this

eval "booking Date"=strptime(timeStr, "%d %m %Y") |sort "Booking Date"

How do I then sort by date?

Labels (1)
0 Karma

AnguaSec
Loves-to-Learn Lots

I replaced timeStr with my field name is that right?  It still doesn't work.  

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @AnguaSec,

this is an example to adapt to your needs.

To help you, could you share your search and a sample of data?

Ciao.

Giuseppe

0 Karma

AnguaSec
Loves-to-Learn Lots

Hi gcusello

Thanks for helping on this.

The original search I did trying to sort by date is 

source="Book7.csv" host="xxxxx" sourcetype="csv"| chart sum(Cost) sum(Total) over "Booking Date" | eval "booking Date"=strptime(timeStr, "%d %m %Y") |sort "Booking Date"

The data is from a column with booking dates, a column with costs and column with the total costs.

 

AnguaSec_0-1603273764826.png

When I changed the last bit it still did the same thing, just sorts by day.

 

Thanks

 

 

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @AnguaSec,

beware that field names are case sensitive and in your search you have:

"booking Date" instead of "Booking Date"

Then it's wrong the time format in strptime function.

So try something like this:

source="Book7.csv" host="xxxxx" sourcetype="csv"
| chart sum(Cost) AS Cost sum(Total) As Total over "Booking Date" 
| eval "Booking Date"=strptime(timeStr,"%d/%m/%Y") 
| sort "Booking Date"
| eval "Booking Date"=strftime(timeStr,"%d/%m/%Y") 

Two additional hints:

  • use always "index=your_index" in your main search: you'll have quicker searches,
  • if possible, avoid fields with spaces in the field names: "Booking_Date" is better than "Booking Date", at the end of the search you can rename that field using the label you like.

Ciao.

Giuseppe

0 Karma

AnguaSec
Loves-to-Learn Lots

Hi gcusello

Thank you.  I know the fields are case sensitive but for some reason when I change them all to be the same the date disappears completely.  See below.

AnguaSec_0-1603280361222.png

When I change all but the first to b instead of B it works

over "Booking Date" | eval "booking Date"=strptime(timeStr,"%d/%m/%Y")
| sort "booking Date"
| eval "booking Date"=strftime(timeStr,"%d/%m/%Y")

AnguaSec_1-1603280466302.png

but that still won't sort the booking dates in order.

Although I know it's better to not have gaps in the field names the field names have come from the spreadsheet but it's too complicated to work on sorting and renaming at the same time so I want to sort the date order issue out first.

Thanks

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @AnguaSec,

in my Splunk it runs but probably I have different data!

Anyway, please try this:

source="Book7.csv" host="xxxxx" sourcetype="csv"
| eval "Booking Date"=strptime(timeStr,"%d/%m/%Y") 
| chart sum(Cost) AS Cost sum(Total) As Total over "Booking Date" 
| sort -"Booking Date"
| eval "Booking Date"=strftime(timeStr,"%d/%m/%Y") 

Ciao.

Giuseppe

0 Karma

AnguaSec
Loves-to-Learn Lots

Hi gcusello

I've managed to sort the data in date order by changing the date to epoch time which works great for the Statistics page but because the Epoch Time is showing on the graph it won't show the costs on the graph as well.   Any idea how to remove the epoch time after adding it without it taking away all the data?

This is the query i used to enable sorting by date

source="Book7.csv" host="xxxx" sourcetype="csv"| chart sum(Cost) sum(Total) over "Booking Date" | rename "Booking Date" AS Booking_Date | convert timeformat="%d/%m/%Y" mktime(Booking_Date) as "ConvertedEpochTime" | sort "ConvertedEpochTime"

This is how the data looks in Statistics

AnguaSec_0-1603287032817.png

 

So I need to remove the data in the last column for the graphs.  Any ideas?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @AnguaSec,

did you already tried with

| fields - ConvertedEpochTime

?

Ciao.

Giuseppe

0 Karma

AnguaSec
Loves-to-Learn Lots

I Hadn't.  That worked.   Thank you so much.   Got there in the end 

:):):)

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @AnguaSec,

good for you,

if this answer solves your need, please, accept it for the other people of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma

AnguaSec
Loves-to-Learn Lots

It's strange isn't it?

Thank you for trying to help but that's not worked either.  I think I'll have to rethink how we use the original data.

 

Many thanks

0 Karma

AnguaSec
Loves-to-Learn Lots

Yes the date format is dd/mm/yyyy.

Where in the query you've given the example on would I put the field name?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @AnguaSec,

what's the forma of your timeStr field?

if it's dd/mm/yyyy, you should try something like this:

| eval epoch_timeStr=strptime(timeStr,"%d/%m/Y")
| sort epoch_timeStr

in other words, the formt to use in strptime is the one of the field timeStr.

To know the time variables see at https://docs.splunk.com/Documentation/Splunk/8.0.6/SearchReference/Commontimeformatvariables

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...