topic Re: Summing epoch values within a JSON field in Getting Data In

Summing epoch values within a JSON field

Esky73 — Fri, 20 Oct 2017 03:03:12 GMT

I have ingested a JSON file which shows me how long spent on an app on my phone and looks like (below)
The fields have been extracted using KV_MODE = json which extracts the fields.
The "tc" field consists of arrays of start epoch time and stop epoch time.
Looking for a way to calculate the duration between start and stop time and sum the totals of the time pairs ?

{ "id": "4bd3f831", "pn": "com.mixcloud.player414", "pvc": "414", "tc": [ [ 1508278704589, 1508278705604 ], [ 1508278705751, 1508278707123 ], [ 1508278707180, 1508278708056 ], [ 1508278708101, 1508278708927 ], [ 1508278709604, 1508278716390 ], [ 1508278836263, 1508278837301 ], [ 1508278837693, 1508278851610 ], [ 1508278861521, 1508278862569 ], [ 1508278862931, 1508278868520 ], [ 1508302373918, 1508302374962 ], [ 1508302375336, 1508302383102 ], [ 1508363224848, 1508363225862 ], [ 1508363226458, 1508363235766 ], [ 1508404509296, 1508404510357 ], [ 1508404510843, 1508404520545 ], [ 1508404520674, 1508404528311 ] ] },

Thx.

Re: Summing epoch values within a JSON field

DalJeanis — Fri, 20 Oct 2017 04:32:31 GMT

Try this...

| streamstats count as recno
| appendpipe 
    [| fields recno 
     | rex field=_raw "\[\s*(?<starttime>\d+),\s*(?<stoptime>\d+)\s*\]" max_match=0
     | eval myFan=mvrange(0,mvcount(starttime)) 
     | mvexpand myFan 
     | eval duration=tonumber(mvindex(stoptime,myFan)) - tonumber(mvindex(starttime,myFan))
     | stats sum(duration) as duration count as killme by recno
    ]
| eventstats values(duration)  as duration by recno
| where isnull(killme)

updated typo +* to *, updated eval duration to use tonumber() on the results before subtracting.

Re: Summing epoch values within a JSON field

Esky73 — Fri, 20 Oct 2017 05:12:43 GMT

Thanks @DalJeanis .. there was a slight typo in the rex cmd :
amended to - rex field=_raw "\[\s*(?\d+),\s*(?\d+)\s*\]" max_match=0
But now i see the following error ..
Error in 'eval' command: Typechecking failed. '-' only takes numbers.

Re: Summing epoch values within a JSON field

Esky73 — Sun, 22 Oct 2017 22:33:02 GMT

separated the eval cmd which worked with the following - thx ...

index=test pn="*"
| streamstats count as recno 
| appendpipe 
    [| fields recno 
    | rex field=_raw "\[\s*(?<starttime>\d+),\s*(?<stoptime>\d+)\s*\]" max_match=0 
    | eval myFan=mvrange(0,mvcount(starttime)) 
    | mvexpand myFan 
    | eval stopduration=mvindex(stoptime,myFan) 
    | eval startduration=mvindex(starttime,myFan) 
    | eval duration=stopduration-startduration 
    | stats sum(duration) as duration count as killme by recno
    ] 
| eventstats values(duration) as duration by recno 
| where isnull(killme)

Re: Summing epoch values within a JSON field

DalJeanis — Mon, 23 Oct 2017 00:28:45 GMT

@Esky73 - interesting.

I would have done this, but your way is fine.

  | eval duration=tonumber(mvindex(stoptime,myFan)) - tonumber(mvindex(starttime,myFan))

Updated the answer to fix the typo and add the the tonumber() typing.

Please accept the answer so it will show as closed.

Re: Summing epoch values within a JSON field

Esky73 — Thu, 02 Nov 2017 23:59:38 GMT

With the output from the above search - how to sum the duration of the same processes so sum of com.teslcoilsw.launcher.* and sum of com.facebook.katana.* ?

1 com.teslacoilsw.launcher54100 391.27
2 com.teslacoilsw.launcher54100 382.81
3 com.teslacoilsw.launcher54100 285.94
4 com.teslacoilsw.launcher53000 270.90
5 com.facebook.katana75931115 102.39
6 com.whatsapp452018 89.20
7 com.facebook.katana75931115 75.37
8 com.facebook.katana74851857 58.64