Splunk Search

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

timcolpo
Explorer

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.

0 Karma

timcolpo
Explorer

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"

0 Karma

timcolpoATT
New Member

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"

0 Karma

woodcock
Esteemed Legend

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

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...