query:
index=ctap source="/charter/apps/gwtrbl/logs/troubleshooting*.log" host=sc58laopp0* End of Branch Execution : WorkflowLogger accountNumber="8245*" | eval it = strptime(branchExecutionStartTime, "%a %b %e %H:%M:%S %Z %Y") | eval ot = strptime(BranchExeEndTime, "%a %b %e %H:%M:%S %Z %Y") | eval duration= tostring((ot - it), "duration") | lookup branchIdsNew.csv branchId | table accountNumber treeId branchId BRANCH_ABBREV branchExecutionStartTime, BranchExeEndTime, duration _time | sort - _time
Results:
accountNumber treeId branchId BRANCH_ABBREV branchExecutionStartTime BranchExeEndTime duration _time
8.24512E+15 DT0000000001716 DTB000000014925 The customer's issue has been resolved Tue Jul 12 07:43:30 CDT 2016 Tue Jul 12 07:43:30 CDT 2016 00:00.0 2016-07-12T07:43:30.899-0500
8.24512E+15 DT0000000001716 DTB000000014924 Does customer have dial tone now Tue Jul 12 07:43:30 CDT 2016 Tue Jul 12 07:43:30 CDT 2016 00:00.0 2016-07-12T07:43:30.791-0500
8.24512E+15 DT0000000001716 DTB000000014898 Provisioning Check Tue Jul 12 07:43:24 CDT 2016 Tue Jul 12 07:43:27 CDT 2016 00:03.0 2016-07-12T07:43:27.617-0500
8.24512E+15 DT0000000001716 DTB000000014924 Does customer have dial tone now Tue Jul 12 07:43:22 CDT 2016 Tue Jul 12 07:43:24 CDT 2016 00:02.0 2016-07-12T07:43:24.699-0500
8.24512E+15 DT0000000001716 DTB000000014922 Have customer perform a power cycle Tue Jul 12 07:41:51 CDT 2016 Tue Jul 12 07:43:22 CDT 2016 01:31.0 2016-07-12T07:43:22.167-0500
8.24512E+15 DT0000000001716 DTB000000014921 Does modem have any lights on/blinking Tue Jul 12 07:41:46 CDT 2016 Tue Jul 12 07:41:51 CDT 2016 00:05.0 2016-07-12T07:41:51.280-0500
8.24512E+15 DT0000000001716 DTB000000014917 Has cust moved modem or added splitters Tue Jul 12 07:41:40 CDT 2016 Tue Jul 12 07:41:45 CDT 2016 00:05.0 2016-07-12T07:41:45.995-0500
8.24512E+15 DT0000000001716 DTB000000014890 Does cust have issues with any other services Tue Jul 12 07:41:38 CDT 2016 Tue Jul 12 07:41:40 CDT 2016 00:02.0 2016-07-12T07:41:40.576-0500
8.24512E+15 DT0000000001716 DTB000000015662 Determine if the customer is at home Tue Jul 12 07:41:29 CDT 2016 Tue Jul 12 07:41:38 CDT 2016 00:09.0 2016-07-12T07:41:38.414-0500
8.24512E+15 DT0000000001716 DTB000000014899 Verify if the customer's modem is online Tue Jul 12 07:39:34 CDT 2016 Tue Jul 12 07:41:29 CDT 2016 01:55.0 2016-07-12T07:41:29.401-0500
8.24512E+15 DT0000000001716 DTB000000014897 DRUM Check Tue Jul 12 07:39:30 CDT 2016 Tue Jul 12 07:39:34 CDT 2016 00:04.0 2016-07-12T07:39:34.253-0500
8.24512E+15 DT0000000001716 DTB000000014888 Outage Check Tue Jul 12 07:39:30 CDT 2016 Tue Jul 12 07:39:30 CDT 2016 00:00.0 2016-07-12T07:39:30.418-0500
8.24512E+15 DT0000000001716 DTB000000014876 Billing Check Tue Jul 12 07:39:29 CDT 2016 Tue Jul 12 07:39:29 CDT 2016 00:00.0 2016-07-12T07:39:29.950-0500
How do I sum the duration values to create Total_duration?
Try like this
index=ctap source="/charter/apps/gwtrbl/logs/troubleshooting*.log" host=sc58laopp0* End of Branch Execution : WorkflowLogger accountNumber="8245*" | eval it = strptime(branchExecutionStartTime, "%a %b %e %H:%M:%S %Z %Y") | eval ot = strptime(BranchExeEndTime, "%a %b %e %H:%M:%S %Z %Y") | eval duration= (ot - it) | lookup branchIdsNew.csv branchId | table accountNumber treeId branchId BRANCH_ABBREV branchExecutionStartTime, BranchExeEndTime, duration _time | sort - _time
| eventstats sum(duration) as Total_duration | eval duration= tostring(duration, "duration") | eval Total_duration = tostring(Total_duration , "duration")
Try like this
index=ctap source="/charter/apps/gwtrbl/logs/troubleshooting*.log" host=sc58laopp0* End of Branch Execution : WorkflowLogger accountNumber="8245*" | eval it = strptime(branchExecutionStartTime, "%a %b %e %H:%M:%S %Z %Y") | eval ot = strptime(BranchExeEndTime, "%a %b %e %H:%M:%S %Z %Y") | eval duration= (ot - it) | lookup branchIdsNew.csv branchId | table accountNumber treeId branchId BRANCH_ABBREV branchExecutionStartTime, BranchExeEndTime, duration _time | sort - _time
| eventstats sum(duration) as Total_duration | eval duration= tostring(duration, "duration") | eval Total_duration = tostring(Total_duration , "duration")
Add the group by fields in eventstats as required.