Splunk Search

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

Path Finder

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(infomaxtime) AS Date | chart count over Date by orig_host

infomaxtime is an epoch timestamp,

I've tried various | sort options (time, Date, _indextime, infomax_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 🙂

SplunkTrust
SplunkTrust

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

Path Finder

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

0 Karma

Legend

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

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

Bye.
Giuseppe

0 Karma

Path Finder

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 infomaxtime 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

0 Karma

Legend

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

Bye.
Giuseppe

0 Karma

Path Finder

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" orighost=dc earliest=-30d@d latest=@d | convert timeformat=" %m/%d/%y" ctime(infomaxtime) AS Date | chart count over Date by orighost

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, searchname="Summary - DC Daily Event Count", searchnow=1476090900.000, infomintime=1475794800.000, infomaxtime=1475881200.000, infosearchtime=1476090917.956, orighost=DCNAME, psrsvdgc=2389084, psrsvdv=1, EventCount="SummaryEventCount"

0 Karma