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.
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
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
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
@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!