Splunk Search

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

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

capilarity
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

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...