Transposing multiple DateTime fields into counts per Dates/Hour

[johnwilling]

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.

[johnwilling · Accepted solution]

After much staring, I figured out that Values from fieldsummary is NOT a multivalue....So I had to parse the string to break it up and re-arrange it. Added this to the end...

| eval Stuff=split(replace(replace(values, "}\]", ""), "\[{", ""), "},{")
| table Status Stuff
| mvexpand Stuff
| rex field=Stuff "\"value\":\"(?<DateHour>[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2})\",\"count\":(?<Count>[0-9]*)"
| table DateHour Status Count
| chart sum(Count) as Counts by DateHour, Status

[johnwilling]

I found that fieldsummary has some limits for what it will count. so I had to separate the fields into [{time,state}{time,state}..] then split the data at the "}{" characters then Expand the results into time,state then count each type of state.

| eval States="[" +
if(isnotnull(INITIAL),    "{" + INITIAL     + ",INITIAL}",     "") +
if(isnotnull(TRANSLATING),"{" + TRANSLATING + ",TRANSLATING}", "") + 
if(isnotnull(COMPLETE),   "{" + COMPLETE    + ",COMPLETE}",    "")  +
if(isnotnull(TERMINAL),   "{" + TERMINAL    + ",TERMINAL}",    "")  +
if(isnotnull(CANCELLED),  "{" + CANCELLED   + ",CANCELLED}",   "")  +
if(isnotnull(DUPLICATE),  "{" + DUPLICATE   + ",DUPLICATE}",   "") + "]"
| eval Stuff=split(replace(replace(States, "}\]", ""), "\[{", ""), "}{")
| mvexpand Stuff
| rex field=Stuff "(?<DateHour>[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}),(?<Status>[A-Za-z]*)"
| table DateHour Status
| stats 
count(eval(Status="INITIAL")) as INITIAL,
count(eval(Status="TRANSLATING")) as TRANSLATING,
count(eval(Status="COMPLETE")) as COMPLETE,
count(eval(Status="TERMINAL")) as TERMINAL,
count(eval(Status="CANCELLED")) as CANCELLED,
count(eval(Status="DUPLICATE")) as DUPLICATE,
by DateHour

[aaraneta_splunk]

@johnwilling - Did your answer provide a working solution to your question? If yes and you would like to close out your post, don't forget to click "Accept". But if you'd like to keep it open for possibilities of other answers, you don't have to take action on it yet. Thanks!