We have events that have multiple time values to record timings of a translation. We are looking to show a count of each state per hour.
Sample Data:
|makeresults | eval row="INITIAL,2017-06-23 01:00:01,PREPARING,2017-06-23 01:01:02,SCHEDULED,2017-06-23 01:02:03,TRANSLATING,2017-06-23 01:03:04,LOADING,2017-06-23 01:04:05,COMPLETE,2017-06-23 01:05:06"
|append [makeresults |eval row="INITIAL,2017-06-23 01:01:02,PREPARING,2017-06-23 01:02:03,SCHEDULED,2017-06-23 02:03:04,TRANSLATING,2017-06-23 02:04:05,LOADING,2017-06-23 02:05:06,COMPLETE,2017-06-23 03:06:07"]
|append [makeresults |eval row="INITIAL,2017-06-23 01:02:03,PREPARING,2017-06-23 02:03:04,SCHEDULED,2017-06-23 02:04:05,TRANSLATING,2017-06-23 03:05:06,LOADING,2017-06-23 03:06:07,COMPLETE,2017-06-23 03:07:08"]
|append [makeresults |eval row="INITIAL,2017-06-23 02:03:04,PREPARING,2017-06-23 02:04:05,SCHEDULED,2017-06-23 03:05:06,TRANSLATING,2017-06-23 03:06:07,LOADING,2017-06-23 03:07:08,COMPLETE,2017-06-23 04:08:09"]
|append [makeresults |eval row="INITIAL,2017-06-23 02:04:05,PREPARING,2017-06-23 02:05:06,SCHEDULED,2017-06-23 03:06:07,TRANSLATING,2017-06-23 03:07:08,LOADING,2017-06-23 03:08:09,COMPLETE,2017-06-23 04:09:10"]
|append [makeresults |eval row="INITIAL,2017-06-23 02:05:06,PREPARING,2017-06-23 03:06:07,SCHEDULED,2017-06-23 03:07:08,TRANSLATING,2017-06-23 04:08:09,LOADING,2017-06-23 04:09:10,COMPLETE,2017-06-23 05:00:11"]
|append [makeresults |eval row="INITIAL,2017-06-23 03:06:07,PREPARING,2017-06-23 04:07:08,SCHEDULED,2017-06-23 05:08:09,TRANSLATING,2017-06-23 05:09:10,LOADING,2017-06-23 05:10:11,COMPLETE,2017-06-23 05:01:12"]
|append [makeresults |eval row="INITIAL,2017-06-23 03:07:08,PREPARING,2017-06-23 04:08:09,SCHEDULED,2017-06-23 05:09:10,TRANSLATING,2017-06-23 05:09:11,LOADING,2017-06-23 06:11:12,COMPLETE,2017-06-23 06:02:13"]
|append [makeresults |eval row="INITIAL,2017-06-23 04:08:09,PREPARING,2017-06-23 05:09:10,SCHEDULED,2017-06-23 05:00:11,TRANSLATING,2017-06-23 06:01:12,LOADING,2017-06-23 06:12:13,COMPLETE,2017-06-23 06:03:14"]
|append [makeresults |eval row="INITIAL,2017-06-23 06:09:10,PREPARING,2017-06-23 06:10:11,SCHEDULED,2017-06-23 06:01:12,TRANSLATING,2017-06-23 06:02:13,LOADING,2017-06-23 06:13:14,COMPLETE,2017-06-23 06:04:15"]
Desired Output:
DateHour INITIAL PREPARING SCHEDULED TRANSLATING LOADING COMPLETE
2017-06-23 01 3 2 1 1 1 1
2017-06-23 02 3 3 2 1 1
2017-06-23 03 2 1 3 3 2 2
2017-06-23 04 1 2 1 1 2
2017-06-23 05 1 3 2 1 2
2017-06-23 06 1 1 1 2 2 3
To convert the Date & Time to Date and Hour I use ...
| eval mvStatus=mvindex(split(row, ","), 0, -1),
INITIAL=mvindex(split(mvindex(mvStatus,mvfind(mvStatus, "INITIAL")+1),":"),0,0),
PREPARING=mvindex(split(mvindex(mvStatus,mvfind(mvStatus, "PREPARING")+1),":"),0,0),
SCHEDULED=mvindex(split(mvindex(mvStatus,mvfind(mvStatus, "SCHEDULED")+1),":"),0,0),
TRANSLATING=mvindex(split(mvindex(mvStatus,mvfind(mvStatus, "TRANSLATING")+1),":"),0,0),
LOADING=mvindex(split(mvindex(mvStatus,mvfind(mvStatus, "LOADING")+1),":"),0,0),
COMPLETE=mvindex(split(mvindex(mvStatus,mvfind(mvStatus, "COMPLETE")+1),":"),0,0)
| table INITIAL PREPARING SCHEDULED TRANSLATING LOADING COMPLETE
Now I can get close with the following...
| fieldsummary
| fields field values
field values
COMPLETE [{"value":"2017-06-23 06","count":3},{"value":"2017-06-23 03","count":2},{"value":"2017-06-23 04","count":2},{"value":"2017-06-23 05","count":2},{"value":"2017-06-23 01","count":1}]
INITIAL [{"value":"2017-06-23 01","count":3},{"value":"2017-06-23 02","count":3},{"value":"2017-06-23 03","count":2},{"value":"2017-06-23 04","count":1},{"value":"2017-06-23 06","count":1}]
LOADING [{"value":"2017-06-23 03","count":3},{"value":"2017-06-23 06","count":3},{"value":"2017-06-23 01","count":1},{"value":"2017-06-23 02","count":1},{"value":"2017-06-23 04","count":1},{"value":"2017-06-23 05","count":1}]
PREPARING [{"value":"2017-06-23 02","count":3},{"value":"2017-06-23 01","count":2},{"value":"2017-06-23 04","count":2},{"value":"2017-06-23 03","count":1},{"value":"2017-06-23 05","count":1},{"value":"2017-06-23 06","count":1}]
SCHEDULED [{"value":"2017-06-23 03","count":3},{"value":"2017-06-23 05","count":3},{"value":"2017-06-23 02","count":2},{"value":"2017-06-23 01","count":1},{"value":"2017-06-23 06","count":1}]
TRANSLATING [{"value":"2017-06-23 03","count":3},{"value":"2017-06-23 05","count":2},{"value":"2017-06-23 06","count":2},{"value":"2017-06-23 01","count":1},{"value":"2017-06-23 02","count":1},{"value":"2017-06-23 04","count":1}]
So how do I extract the Multivalue parts, extracts the date/hour, and associate the count to the State and do a:
stats sum(INITIAL), ...sum(COMPLETE) by DateHour
Running under:
Splunk Version 6.4.0
Splunk Build f2c836328108
Thanks.
... View more