Sorting graphs by UK date format (dd/mm/yy)

[capilarity]

I'm running a summary index tracking event counts from a number of servers for each day.
I'm trying to put up a dashboard that tracks the number in events by day and by servers over the past 30 days. The search works fine, but when I try to chart this, the date format is all messed up because it only sorts on the day field of the time stamp.

index=summary source="summary-search earliest=-30d@d latest=@d | convert timeformat="%d/%m/%y" ctime(info_max_time) AS Date | chart count over Date by orig_host

info_max_time is an epoch timestamp,

I've tried various | sort options (_time, Date, _indextime, info_max_time) after the chart command that all work in tables but not for charts.

Managers like pretty graphs and the US date format confuses them 🙂

[somesoni2]

Give this a try

index=summary source="summary-search earliest=-30d@d latest=@d | eval _time=info_max_time
| timechart span=1d count by orig_host limit=0
| fieldformat _time=strftime(_time, "%d/%m/%y")

[capilarity]

Thanks for the suggestion somesoni2. This sort of works but it always displays the first data point as Zero for all hosts and doesn't display the dates on the x -axis. If I can sort out the first data point issue, this should be fine for a dashboard.

Thanks

[gcusello]

Hi capilarity,
you should try to create the chart before convert your date and after do the conversion, something like this:

your_search | bin info_max_time span=1d| chart count over info_max_time by host | eval _time=strftime(info_max_time,"%d/%m/%y")

Bye.
Giuseppe

[capilarity]

Thanks Giuseppe, I had tried something similar myself, but its displays the epoch time on the graph instead of a date stamp, and this is for managers who wont understand epoch....
Also if i use "chart count over info_max_time by orig_host" to show all the servers, it shows _time as a host and adds and extra line for it!! Any ideas why that might be happening?

Thanks

[gcusello]

To convert a date from epochtime to human readable format use
| eval info_max_time=strftime(info_max_time,"%d/%m/%y")
about the second answer, please share your search.

Bye.
Giuseppe

[capilarity]

but converting in back to a human form reverts to the original problem in that the ordering is wrong is UK date format.
The x axis is currently ordered on the first two digits so the months are out of order- ie
01/10/16 02/10/16 03/10/16 ...... 08/10/16 09/10/16 10/09/16 11/09/16 etc
Second answer - search is:
index=summary source="Summary - DC Daily Event Count" orig_host=dc earliest=-30d@d latest=@d | convert timeformat=" %m/%d/%y" ctime(info_max_time) AS Date | chart count over Date by orig_host

There are a number of domain controllers and we run a summary search overnight for the previous days stats:
index=wineventlog source="WinEventLog:Security" host="DC" | sistats count by host

generates a summary index event like this:(_raw event)

10/07/2016 00:00:00 +0100, search_name="Summary - DC Daily Event Count", search_now=1476090900.000, info_min_time=1475794800.000, info_max_time=1475881200.000, info_search_time=1476090917.956, orig_host=DC_NAME, psrsvd_gc=2389084, psrsvd_v=1, EventCount="Summary_Event_Count"