Splunk Search

How to set a bar chart by quarter?

danielbb
Motivator

I have the following code that shows leases that end in June.

| inputlookup Leases.csv  
| rename "Lease End" as leaseEnd 
| eval timestamp=strptime(leaseEnd, "%Y-%m-%d") 
| eval Day=strftime(timestamp,"%d"), Month=strftime(timestamp,"%m"), Year=strftime(timestamp,"%Y") 
| where Year = 2020 AND Month = 6

The requirement is to update the query to return leases ending by quarter i.e. Q1, Q2, Q3, Q4 2020 and display in a bar chart by quarter.

How can I do that? I don't know how to aggregate the data by quarter.

Tags (2)
0 Karma
1 Solution

to4kawa
Ultra Champion

| inputlookup Leases.csv

| rename "Lease End" as leaseEnd
| eval timestamp=strptime(leaseEnd, "%Y-%m-%d")
| eval Day=strftime(timestamp,"%d"), Month=strftime(timestamp,"%m"), Year=strftime(timestamp,"%Y")
| eval Quater=ceil(tonumber(Month)/3)."Q"
| where Year="2020"
| stats max(timestamp) as timestamp by Quater
| convert ctime(timestamp)
but Bar Chart?
Bar should be numeric. not string or text.

View solution in original post

to4kawa
Ultra Champion

| inputlookup Leases.csv

| rename "Lease End" as leaseEnd
| eval timestamp=strptime(leaseEnd, "%Y-%m-%d")
| eval Day=strftime(timestamp,"%d"), Month=strftime(timestamp,"%m"), Year=strftime(timestamp,"%Y")
| eval Quater=ceil(tonumber(Month)/3)."Q"
| where Year="2020"
| stats max(timestamp) as timestamp by Quater
| convert ctime(timestamp)
but Bar Chart?
Bar should be numeric. not string or text.

danielbb
Motivator

This is great - | eval Quater=ceil(tonumber(Month)/3)."Q"

0 Karma

danielbb
Motivator

Thank you @to4kawa and I ended up with -

| inputlookup Leases.csv 
| rename "Lease End" as leaseEnd 
| eval timestamp=strptime(leaseEnd, "%Y-%m-%d") 
| eval Day=strftime(timestamp,"%d"), Month=strftime(timestamp,"%m"), Year=strftime(timestamp,"%Y") 
| eval Quarter=ceil(tonumber(Month)/3)."Q"
| eval nn =  Year + " " + Quarter
| stats count by nn
0 Karma

to4kawa
Ultra Champion

I see, Happy Splunking!

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...