Sort Date in header

[katrinamara]

I don't know what's wrong with my code. I cannot sort the date using sort.
Below is my code. I need to sort it by Date.

index="sharepoint_capacity"
| fields - _raw 
| fields "Resource Name" "Capacity End Date" "Capacity Start Date" EID FTE "Project Name" ID 
| stats count by "Resource Name" FTE "Project Name"
| rename FTE as Allocation 
| join
    [search index="sharepoint_capacity" 
    |eval epoch_end=strptime('Capacity End Date',"%m/%d/%Y"), epoch_start=strptime('Capacity Start Date',"%m/%d/%Y"), between=mvrange(epoch_start,epoch_end,"1d") 
| mvexpand between 
| eval _time=between 
| stats values(FTE) as alloc by "Resource Name" FTE "Project Name" _time
| eval months=strftime(_time,"%B %d %Y") 
| xyseries "Resource Name" months alloc
| transpose 0 header_field="Resource Name" 
| rename column as Month
| eval getmonth=strptime(Month,"%B %d %Y")
| sort getmonth
| transpose 0 header_field=Month 
| rename column as "Resource Name"]
| fields - count 

[katrinamara · Accepted solution]

Here's my final query which fixed my issue.

index="sharepoint_capacity"
| fields - _raw 
| fields "Resource Name" "Capacity End Date" "Capacity Start Date" EID FTE "Project Name" ID 
| stats count by "Resource Name" FTE "Project Name"
| rename FTE as Allocation 
| join
    [search index="sharepoint_capacity" 
    |eval epoch_end=strptime('Capacity End Date',"%m/%d/%Y"), epoch_start=strptime('Capacity Start Date',"%m/%d/%Y"), between=mvrange(epoch_start,epoch_end,"1d") 
| mvexpand between 
| eval _time=between 
| stats values(FTE) as alloc by "Resource Name" FTE "Project Name" _time
| eval months=strftime(_time,"%B %d %Y") 
| xyseries "Resource Name" months alloc]
| fields - count 
| transpose 0
| eval column1=column, getmonth=strptime(column1,"%B %d %Y") 
| fillnull value=1 getmonth
| sort getmonth
| fields - column1 getmonth
| transpose 0 header_field=column 
| fields - column
| sort "Resource Name"

[richgalloway]

@katrinamara if your problem is resolved, please accept an answer to help future readers.

[niketn]

@katrinamara do you mind sharing some mocked up sample events and also the current output vs desired output?

[katrinamara]

Hi, already done resolving this issue. thanks!

[niketn]

Good to know, to me join on same index and then xyseries along with two times transpose seemed excessive effort 🙂 So wanted to see if what is the output you wanted and whether there was any other way. In any case do post your final query which fixed the issue and accept the same as answer. This will mark your question as answered and also help the community.

[Elsurion]

You are sorting in the subsearch not in den main search, try an do the sort in the main search.
To get a formated search in Splunk> just hit Ctrl+F in the inputbox for formatting.

index="sharepoint_capacity" 
| fields - _raw 
| fields "Resource Name" "Capacity End Date" "Capacity Start Date" EID FTE "Project Name" ID 
| stats count by "Resource Name" FTE "Project Name" 
| rename FTE as Allocation 
| join 
    [ search index="sharepoint_capacity" 
    | eval epoch_end=strptime('Capacity End Date',"%m/%d/%Y"), epoch_start=strptime('Capacity Start Date',"%m/%d/%Y"), between=mvrange(epoch_start,epoch_end,"1d") 
    | mvexpand between 
    | eval _time=between 
    | stats values(FTE) as alloc by "Resource Name" FTE "Project Name" _time 
    | eval months=strftime(_time,"%B %d %Y") 
    | xyseries "Resource Name" months alloc 
    | transpose 0 header_field="Resource Name" 
    | rename column as Month 
    | eval getmonth=strptime(Month,"%B %d %Y") 
    | transpose 0 header_field=Month 
    | rename column as "Resource Name"] 
| sort getmonth 
| fields - count