topic Re: Sort by date in Reporting

Sort by date

AnguaSec — Wed, 21 Oct 2020 08:25:42 GMT

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?

Re: Sort by date

gcusello — Wed, 21 Oct 2020 09:00:38 GMT

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

Re: Sort by date

AnguaSec — Wed, 21 Oct 2020 09:32:08 GMT

Yes the date format is dd/mm/yyyy.

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

Re: Sort by date

AnguaSec — Wed, 21 Oct 2020 09:39:06 GMT

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

Re: Sort by date

gcusello — Wed, 21 Oct 2020 09:43:36 GMT

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

Re: Sort by date

AnguaSec — Wed, 21 Oct 2020 09:52:14 GMT

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

Re: Sort by date

gcusello — Wed, 21 Oct 2020 11:13:43 GMT

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

Re: Sort by date

AnguaSec — Wed, 21 Oct 2020 11:44:03 GMT

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

Re: Sort by date

gcusello — Wed, 21 Oct 2020 12:20:34 GMT

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

Re: Sort by date

AnguaSec — Wed, 21 Oct 2020 12:28:10 GMT

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

Re: Sort by date

AnguaSec — Wed, 21 Oct 2020 13:30:57 GMT

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?

Re: Sort by date

gcusello — Wed, 21 Oct 2020 13:36:55 GMT

Hi @AnguaSec,

did you already tried with

| fields - ConvertedEpochTime

Ciao.

Giuseppe

Re: Sort by date

AnguaSec — Wed, 21 Oct 2020 13:39:53 GMT

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

:):):)

Re: Sort by date

gcusello — Wed, 21 Oct 2020 14:30:45 GMT

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 😉