Splunk Search

Creating a table with chronological headers

Stevelim
Communicator

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

Tags (1)
0 Karma

chinmoya
Communicator

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

0 Karma

Stevelim
Communicator

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 |

0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Quantify Your Splunk Investment Impact: Introducing Savings Metrics to Value Insights

Building on the foundation established in our initial Value Insights releases, we are introducing the Savings ...

Event Series: Telemetry Pipeline Management

Balancing Scale and Spend: Gaining Control Over High-Volume Metrics in Splunk Observability Cloud As ...

Kick the Tires Before You Commit: A Hands-On Tour of the Splunk Observability Cloud ...

Evaluating an enterprise observability platform usually goes like this: fill out a form, get a free trial with ...