topic Re: Creating a table with chronological headers in Splunk Search

Creating a table with chronological headers

Stevelim — Wed, 17 Jul 2019 11:42:04 GMT

Given a week worth of timestamped data like the following:

1st FEB
Time = "010219 0100" Category = "A" Value = "1.1"
Time = "010219 0100" Category = "B" Value = "1.3"
Time = "010219 0110" Category = "A" Value = "2.1" <- Last data for A on 1st FEB
Time = "010219 0110" Category = "B" Value = "2.2" <- Last data for B on 1st FEB

2nd FEB
Time = "020219 0100" Category = "A" Value = "1.1"
Time = "020219 0100" Category = "B" Value = "1.3"
Time = "020219 0110" Category = "A" Value = "1.1" <- Last data for A on 2nd FEB
Time = "020219 0110" Category = "B" Value = "1.2" <- Last data for B on 2nd FEB

...

Assuming my keys are extracted correctly, how can I display a table of this format:
Category 01-FEB 02-FEB...
A 2.1 1.1
B 2.2 1.2

I am able to get to this point with the following query:

<Base Search>
| dedup date_mday, date_month, TagName
| eval date = strftime(_time,"%Y%m%d-%b")
| xyseries TagName, date, Value

and get the following results:
Category 20190219-Feb 20190219-Feb
A 2.1 1.1
B 2.2 1.2

How can I rename manipulate the header such that they are all in chronological order with the correct header names? For context, the data will be searched for an entire year. I was able to get close to the presentation but encountered an ordering issue, for example, all the 01-X grouped together, 01-JAN, 01-FEB, 01-MAR and so on

Re: Creating a table with chronological headers

chinmoya — Wed, 30 Sep 2020 01:21:28 GMT

try below:
| eval date = strftime(_time,"%Y%m%d-%b")

| chart latest(Value) over Category by date

An example similar to yours with internal index
index=_internal earliest=-7d@d latest=now| eval display_time = strftime(_time,"%Y%m%d-%b") | chart latest(log_level) over sourcetype by display_time

The strftime conversation can be changed as needed

Re: Creating a table with chronological headers

Stevelim — Mon, 22 Jul 2019 13:37:47 GMT

The issue with this technique is that I need the format to be 22-Jul for example. Changing the eval strtime command to strftime(_time,"%d-%b") works but only for a month of data. I eventually need this to go over a year worth of data which will end up with this arrangement of headers if I go with this:

01-JAN | 01-FEB .. | 01-DEC | 02-JAN |