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
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.
You might want to sanitize your screenshot, looks like that contains some data that may not be suitable for sharing publicly 🙂
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.
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?
try adding |stats values(*) as * by "Resource Name" Allocation "Project Name"
to the end.
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.
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.
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!
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")