Splunk Search

How to make the date as header in table?

katrinamara
Path Finder

I need to do a table which look like this (see below).
alt text

As of now my table look like this
alt text

How can I make the months or dates in header?
Here's my query

index="sharepoint_capacity" 
| dedup ID 
| eval epoch_end=strptime('Capacity End Date',"%m/%d/%Y")+86400, epoch_start=strptime('Capacity Start Date',"%m/%d/%Y"), between=mvrange(epoch_start,epoch_end,"1d") 
| mvexpand between 
| eval middle=strftime(between,"%Y %B"), months=strftime(between,"%B") , _time=middle 
| stats count by "Resource Name", FTE ,"Project Name" middle months 
| rename FTE as Allocation
| eval time_range=case(months="September" OR months="April" OR months="June" OR months="November",30,months="February",28,1=1,31),range=round((count/time_range)*100,2)
| fields - months count time_range
0 Karma
1 Solution

cmerriman
Super Champion

try adding this to the end of your search:

|eval {middle}=range|fields - middle range

you could use months in the eval, you just have to do it before the |fields - months..., if that's the field you want as the header, instead of middle.

View solution in original post

0 Karma

FrankVl
Ultra Champion

You might want to sanitize your screenshot, looks like that contains some data that may not be suitable for sharing publicly 🙂

0 Karma

cmerriman
Super Champion

try adding this to the end of your search:

|eval {middle}=range|fields - middle range

you could use months in the eval, you just have to do it before the |fields - months..., if that's the field you want as the header, instead of middle.

0 Karma

katrinamara
Path Finder

Thanks for replying, just tried the query you gave. Now middle is part of my header.
I just need to combine the range value if the Resource Name and Project Name.

As of now my table looks like this.
Resource Name Allocation Project Name Jan Feb
Employee A 1.0 Proj 1 50
Employee A 1.0 Proj 1 100

Expected output:
Resource Name Allocation Project Name Jan Feb
Employee A 1.0 Proj 1 50 100

Do you have any idea how can I do this?

0 Karma

cmerriman
Super Champion

try adding |stats values(*) as * by "Resource Name" Allocation "Project Name" to the end.

0 Karma

katrinamara
Path Finder

It works thanks!
I just need to sort the date in header. can you also help me to sort it?
Format should be: Nov 2017, Dec 2017, Jan 2018, etc.

0 Karma

cmerriman
Super Champion

sorting dates like that is very tricky because splunk sorts alphanumerically. Generally, I try to keep my dates in the format of yyyy-mm-dd so that it sorts year first, then numeric month, followed by numeric day. I'm not sure if it is possible for you to do that based on reqs or not. if you changed the format of middle before putting it as a header, then sorted, it should work. otherwise, you'll have to use |table "Resource Name" Allocation "Project Name" "Nov 2017" "Dec 2017" "Jan 2018"... and write it all out, which isn't great for dynamic fields.

0 Karma

katrinamara
Path Finder

I agree it is very tricky when it comes in sorting the date. My data should show per month that's why the format yyyy-mm-dd will no be possible. Anyway thanks!

0 Karma

cmerriman
Super Champion

you can exclude the dd on the date format and just do yyyy-mm, i was just using an example if ever needing to go down to day level. in your line 5, try this instead: | eval middle=strftime(between,"%Y-%m")

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...