Untable data form a search used to compute an average duration from events with 2 dates

[timcolpo]

I have the following SPL that is used to compute an average duration from events with 2 dates for the last 3 months.
(I WILL MAKE SOME IMPROVEMENTS ON TIME FOR EFFICIENCY BUT THIS IS AN EXAMPLE)

index="db_index" sourcetype="JDBC_D" (CREATED_DATE=* AND RESOLUTION_DATE=*) (SEVERITY="Severity 1" OR SEVERITY="Severity 2") (DEF_PHASE_FOUND="Integrated Systems Test (IST)")
| eval x_time=strptime(RESOLUTION_DATE,"%d-%b-%Y %H:%M:%S")
| eval RESOLUTION_DATE_YYYYMM=strftime(x_time,"%Y-%m")
| where RESOLUTION_DATE_YYYYMM>="2017-06" AND RESOLUTION_DATE_YYYYMM<="2017-11"
| eval CREATED_DATE_time=strptime(CREATED_DATE,"%d-%b-%Y %H:%M:%S"), RESOLUTION_DATE_time=strptime(RESOLUTION_DATE,"%d-%b-%Y %H:%M:%S")
| eval RESOLVE_DURATION = (RESOLUTION_DATE_time-CREATED_DATE_time)/3600
| eventstats max(RESOLUTION_DATE_time) as max_RESOLUTION_DATE_time by DEFECT_ID RESOLUTION_DATE_YYYYMM
| where RESOLUTION_DATE_time=max_RESOLUTION_DATE_time
| eventstats avg(RESOLVE_DURATION) as avg_RESOLVE_DURATION_YTD
| stats avg(RESOLVE_DURATION) as avg_RESOLVE_DURATION_MTD by RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_YTD
| sort RESOLUTION_DATE_YYYYMM
| tail 3
| eval TEAM="IST", REPORT="MTTRetest"
| lookup ATT_CPVT__REPORTS TEAM AS TEAM REPORT AS REPORT OUTPUT BENCHMARK AS BENCHMARK TARGET AS TARGET DESCRIPTION AS DESCRIPTION
| eval BENCHMARK=split(BENCH)

which gives me

MONTH       avg_RESOLVE_DURATION_MTD .     avg_RESOLVE_DURATION_YTD       TARGET
2017-11 .     15.1                                   13.1                  10
2017-10 .     10.3                                      13.1               10
2017-09 .      7.1                                        13.1              10

AND I WANT TO TABLE THE DATA AS

REPORT TARGET . 2017-09       2017-10 .     2017-10 .     YTD_AVERAGE
MTTR .   10 .           7.1 .              10.3              15.1 .           13.1

I have been working through untable and some stats formats but cannot seem to get this working.

[timcolpo]

Thanks for the answers I will give them a try and will also do a comparison on efficiency. The following is one way I got it to work but I am not 100% sure on the merit of the approach

index="db_index" sourcetype="JDBC_defects" (CREATED_DATE=* AND RESOLUTION_DATE=) (SEVERITY="Severity 1" OR SEVERITY="Severity 2") (DEF_PHASE_FOUND="Integrated Systems Test (IST)")
| eval x_time=strptime(RESOLUTION_DATE,"%d-%b-%Y %H:%M:%S")
| eval RESOLUTION_DATE_YYYYMM=strftime(x_time,"%Y-%m")
| where RESOLUTION_DATE_YYYYMM>="2017-06" AND RESOLUTION_DATE_YYYYMM<="2017-11"
| eval CREATED_DATE_time=strptime(CREATED_DATE,"%d-%b-%Y %H:%M:%S"), RESOLUTION_DATE_time=strptime(RESOLUTION_DATE,"%d-%b-%Y %H:%M:%S")
| eval RESOLVE_DURATION = (RESOLUTION_DATE_time-CREATED_DATE_time)/3600
| eventstats max(RESOLUTION_DATE_time) as max_RESOLUTION_DATE_time by DEFECT_ID RESOLUTION_DATE_YYYYMM
| where RESOLUTION_DATE_time=max_RESOLUTION_DATE_time
| eventstats avg(RESOLVE_DURATION) as avg_RESOLVE_DURATION_YTD
| stats avg(RESOLVE_DURATION) as avg_RESOLVE_DURATION_MTD by RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_YTD
| sort RESOLUTION_DATE_YYYYMM
| tail 3
| eval TEAM="IST", REPORT="MTTRetest"
| eval TEAMREPORT=TEAM."~".REPORT."~".avg_RESOLVE_DURATION_YTD
| xyseries TEAMREPORT RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_MTD
| appendcols
[| makeresults
| eval TEAM="IST", REPORT="MTTRetest"
| eval TEAMREPORT=TEAM." ".REPORT
| lookup ATT_CPVT__REPORTS TEAM AS TEAM REPORT AS REPORT OUTPUT BENCHMARK AS BENCHMARK TARGET AS TARGET DESCRIPTION AS DESCRIPTION
| eval BENCHMARK=split(BENCHMARK,"~"), TARGET=split(TARGET,"~")]
| rex field=TEAMREPORT "(?[^~])~(?[^~])~(?.)"
| fields - TEAMREPORT
| rename avg_RESOLVE_DURATION_YTD AS "AVERAGE RESOLVE DURATION YTD"
| fields TEAM REPORT DESCRIPTION BENCHMARK TARGET * "AVERAGE RESOLVE DURATION YTD"

[timcolpoATT]

Thank you. I did manage to find a way to do but I do not like it. Part of the problem is that addressing the specific 3 columns is subjective. In order to order the table I had to code
| fields TEAM REPORT DESCRIPTION BENCHMARK TARGET * "AVERAGE RESOLVE DURATION YTD"

The following is the SPL that achieves the goal and I will also take a look at the solutions and ideas provided.

index="db_index" sourcetype="JDBC_defects" (CREATED_DATE=* AND RESOLUTION_DATE=) (SEVERITY="Severity 1" OR SEVERITY="Severity 2") (DEF_PHASE_FOUND="Integrated Systems Test (IST)")
| eval x_time=strptime(RESOLUTION_DATE,"%d-%b-%Y %H:%M:%S")
| eval RESOLUTION_DATE_YYYYMM=strftime(x_time,"%Y-%m")
| where RESOLUTION_DATE_YYYYMM>="2017-06" AND RESOLUTION_DATE_YYYYMM<="2017-11"
| eval CREATED_DATE_time=strptime(CREATED_DATE,"%d-%b-%Y %H:%M:%S"), RESOLUTION_DATE_time=strptime(RESOLUTION_DATE,"%d-%b-%Y %H:%M:%S")
| eval RESOLVE_DURATION = (RESOLUTION_DATE_time-CREATED_DATE_time)/3600
| eventstats max(RESOLUTION_DATE_time) as max_RESOLUTION_DATE_time by DEFECT_ID RESOLUTION_DATE_YYYYMM
| where RESOLUTION_DATE_time=max_RESOLUTION_DATE_time
| eventstats avg(RESOLVE_DURATION) as avg_RESOLVE_DURATION_YTD
| stats avg(RESOLVE_DURATION) as avg_RESOLVE_DURATION_MTD by RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_YTD
| sort RESOLUTION_DATE_YYYYMM
| tail 3
| eval TEAM="IST", REPORT="MTTRetest"
| eval TEAMREPORT=TEAM."~".REPORT."~".avg_RESOLVE_DURATION_YTD
| xyseries TEAMREPORT RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_MTD
| appendcols
[| makeresults
| eval TEAM="IST", REPORT="MTTRetest"
| eval TEAMREPORT=TEAM." ".REPORT
| lookup ATT_CPVT__REPORTS TEAM AS TEAM REPORT AS REPORT OUTPUT BENCHMARK AS BENCHMARK TARGET AS TARGET DESCRIPTION AS DESCRIPTION
| eval BENCHMARK=split(BENCHMARK,"~"), TARGET=split(TARGET,"~")]
| rex field=TEAMREPORT "(?[^~])~(?[^~])~(?.)"
| fields - TEAMREPORT
| rename avg_RESOLVE_DURATION_YTD AS "AVERAGE RESOLVE DURATION YTD"
| fields TEAM REPORT DESCRIPTION BENCHMARK TARGET * "AVERAGE RESOLVE DURATION YTD"

[woodcock]

Building from you already have...

| makeresults 
| eval raw="2017-11 15.1 13.1 10::2017-10 10.3 13.1 10::2017-09 7.1 13.1 10"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<MONTH>\S+)\s+(?<avg_RESOLVE_DURATION_MTD>\S+)\s+(?<avg_RESOLVE_DURATION_YTD>\S+)\s+(?<TARGET>\S+)"
| fields - _time _raw

| rename COMMENT AS "Everything above generates sample events; everything below is your add-on solution"

| eval key = avg_RESOLVE_DURATION_YTD . " " . TARGET
| fields - avg_RESOLVE_DURATION_YTD TARGET
| xyseries key MONTH avg_RESOLVE_DURATION_MTD
| rex field=key "^(?<YTD_AVERAGE>\S+)\s+(?<TARGET>\S+)$"
| eval REPORT="MTTR"
| table REPORT TARGET 2* YTD_AVERAGE

[somesoni2]

Give this a try

index="db_index" sourcetype="JDBC_D" (CREATED_DATE=* AND RESOLUTION_DATE=*) (SEVERITY="Severity 1" OR SEVERITY="Severity 2") (DEF_PHASE_FOUND="Integrated Systems Test (IST)")
| eval x_time=strptime(RESOLUTION_DATE,"%d-%b-%Y %H:%M:%S")
| eval RESOLUTION_DATE_YYYYMM=strftime(x_time,"%Y-%m")
| where RESOLUTION_DATE_YYYYMM>="2017-06" AND RESOLUTION_DATE_YYYYMM<="2017-11"
| eval CREATED_DATE_time=strptime(CREATED_DATE,"%d-%b-%Y %H:%M:%S"), RESOLUTION_DATE_time=strptime(RESOLUTION_DATE,"%d-%b-%Y %H:%M:%S")
| eval RESOLVE_DURATION = (RESOLUTION_DATE_time-CREATED_DATE_time)/3600
| eventstats max(RESOLUTION_DATE_time) as max_RESOLUTION_DATE_time by DEFECT_ID RESOLUTION_DATE_YYYYMM
| where RESOLUTION_DATE_time=max_RESOLUTION_DATE_time
| eventstats avg(RESOLVE_DURATION) as avg_RESOLVE_DURATION_YTD
| stats avg(RESOLVE_DURATION) as avg_RESOLVE_DURATION_MTD by RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_YTD
| sort RESOLUTION_DATE_YYYYMM
| tail 3
| eval TEAM="IST", REPORT="MTTRetest"
| lookup ATT_CPVT__REPORTS TEAM AS TEAM REPORT AS REPORT OUTPUT TARGET AS TARGET
| table REPORT TARGET RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_MTD avg_RESOLVE_DURATION_YTD
| appendpipe [stats values(TARGET) as avg_RESOLVE_DURATION_MTD by REPORT | eval RESOLUTION_DATE_YYYYMM="TARGET" ]
| appendpipe [stats values(avg_RESOLVE_DURATION_YTD) as avg_RESOLVE_DURATION_MTD by REPORT | eval RESOLUTION_DATE_YYYYMM="avg_RESOLVE_DURATION_YTD" ]
| table REPORT RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_MTD
| xyseries REPORT RESOLUTION_DATE_YYYYMM avg_RESOLVE_DURATION_MTD 
| table REPORT TARGET 2* avg_RESOLVE_DURATION_YTD