Splunk Search
Highlighted

How to make the date as header in table?

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
Highlighted

Re: How to make the date as header in table?

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
Highlighted

Re: How to make the date as header in table?

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
Highlighted

Re: How to make the date as header in table?

Super Champion

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

0 Karma
Highlighted

Re: How to make the date as header in table?

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
Highlighted

Re: How to make the date as header in table?

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
Highlighted

Re: How to make the date as header in table?

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
Highlighted

Re: How to make the date as header in table?

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
Highlighted

Re: How to make the date as header in table?

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