Sort by date

[AnguaSec]

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?

[AnguaSec]

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

[gcusello]

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

[AnguaSec]

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.

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

Thanks

[gcusello]

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

[AnguaSec]

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.

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")

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

[gcusello]

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

[AnguaSec]

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

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

[gcusello]

Hi @AnguaSec,

did you already tried with

| fields - ConvertedEpochTime

?

Ciao.

Giuseppe

[AnguaSec]

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

:):):)

[gcusello]

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 😉

[AnguaSec]

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

[AnguaSec]

Yes the date format is dd/mm/yyyy.

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

[gcusello]

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