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

How to make the date as header in table?

katrinamara — Wed, 07 Feb 2018 14:35:06 GMT

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

As of now my table look like this

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

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

cmerriman — Wed, 07 Feb 2018 15:29:25 GMT

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.

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

FrankVl — Wed, 07 Feb 2018 15:38:23 GMT

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

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

katrinamara — Wed, 07 Feb 2018 15:49:57 GMT

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?

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

cmerriman — Wed, 07 Feb 2018 16:20:36 GMT

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

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

katrinamara — Wed, 07 Feb 2018 16:33:36 GMT

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.

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

cmerriman — Wed, 07 Feb 2018 16:40:06 GMT

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.

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

katrinamara — Wed, 07 Feb 2018 16:43:22 GMT

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!

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

cmerriman — Wed, 07 Feb 2018 16:46:05 GMT

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")