Splunk Search

Transposing multiple DateTime fields into counts per Dates/Hour

johnwilling
Explorer

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.

Tags (3)
0 Karma
1 Solution

johnwilling
Explorer

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

View solution in original post

0 Karma

johnwilling
Explorer

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

johnwilling
Explorer

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

aaraneta_splunk
Splunk Employee
Splunk Employee

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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, ...