topic Re: Sort Date in header in Splunk Search

Sort Date in header

katrinamara — Thu, 01 Feb 2018 09:19:35 GMT

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

Re: Sort Date in header

Elsurion — Fri, 02 Feb 2018 09:28:41 GMT

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

Re: Sort Date in header

niketn — Fri, 02 Feb 2018 09:39:08 GMT

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

Re: Sort Date in header

katrinamara — Fri, 02 Feb 2018 09:41:16 GMT

Hi, already done resolving this issue. thanks!

Re: Sort Date in header

niketn — Fri, 02 Feb 2018 09:55:07 GMT

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.

Re: Sort Date in header

katrinamara — Fri, 02 Feb 2018 09:57:41 GMT

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"

Re: Sort Date in header

richgalloway — Fri, 02 Feb 2018 12:41:45 GMT

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