Splunk Search

How to sort search and get rid of the count from LAST_MODIFIED_DATE and have them shown by ACTUAL_START_DATE?

Engager

I have a problem regarding sorting in Splunk.
I want to make automated reports and I want to sort in a calendar the amount of tickets one day.

A ticket has these time stamps:

ACTUAL_END_DATE="2018-10-29 01:00:00.0", 
ACTUAL_START_DATE="2018-10-29 00:00:00.0", 
CLOSED_DATE="2019-06-16 12:56:00.0",  
COMPLETED_DATE="2019-06-06 10:47:46.0",  
EARLIEST_START_DATE="2018-10-23 11:20:42.0",  
LAST_MODIFIED_DATE="2019-06-16 12:56:07.0",  
RFA_DATE="2018-10-23 11:20:42.0", 
RFC_DATE="2018-10-22 15:19:00.0",  
SFA_DATE="2019-06-06 10:47:02.0", 
SFR_DATE="2019-06-06 10:46:52.0",  
SCHEDULED_DATE="2019-06-06 10:47:06.0", 
SCHEDULED_END_DATE="2018-10-29 01:00:00.0", 
SCHEDULED_START_DATE="2018-10-29 00:00:00.0",  
SUBMIT_DATE="2018-10-22 15:18:53.0",  

This is my search:

 stats latest(STATUS_REASON) as STATUS_REASON latest(CHANGE_REQUEST_STATUS) as CHANGE_REQUEST_STATUS latest(_time) as _time latest(CHANGE_TIMING) as CHANGE_TIMING by INFRASTRUCTURE_CHANGE_ID 
| where CHANGE_REQUEST_STATUS !="Cancelled" 
| timechart count span=1D

I sort by two tokens, the earliest is "@mon" and the latest is "now".

Unfortunately, it sorts by LASTMODIFIEDDATE and I have 62 tickets in one day. All that have ACTUALSTARTDATE in different months, as you can change a ticket after it closed to add details.

How can I sort them and get rid of the count from LASTMODIFIEDDATE and have them shown by ACTUALSTARTDATE?

Thanks!

0 Karma
1 Solution

Communicator

@theodorel
earliest = -7d@w1 (looks at the first day of the week)
latest = now

| sort by earliest, latest

try using this

View solution in original post

Communicator

@theodorel
earliest = -7d@w1 (looks at the first day of the week)
latest = now

| sort by earliest, latest

try using this

View solution in original post

Engager

I did this and it worked!

stats latest(STATUSREASON) as STATUSREASON latest(CHANGEREQUESTSTATUS) as CHANGEREQUESTSTATUS latest(time) as _time latest(CHANGETIMING) as CHANGETIMING by INFRASTRUCTURECHANGEID
| where CHANGE
REQUESTSTATUS !="Cancelled"
| eval _time = strptime(ACTUAL
START_DATE, "%Y-%m-%d %H:%M:%S.%N")
| timechart count span=1D

0 Karma