Splunk Search
Highlighted

Sort Date in header

Path Finder

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 
Tags (3)
0 Karma
Highlighted

Re: Sort Date in header

Communicator

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
0 Karma
Highlighted

Re: Sort Date in header

Legend

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




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Sort Date in header

Path Finder

Hi, already done resolving this issue. thanks!

0 Karma
Highlighted

Re: Sort Date in header

Legend

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.




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Sort Date in header

Path Finder

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"

View solution in original post

0 Karma
Highlighted

Re: Sort Date in header

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma