Getting Data In

Summing epoch values within a JSON field

Esky73
Builder

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.

0 Karma
1 Solution

DalJeanis
Legend

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.

View solution in original post

DalJeanis
Legend

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.

Esky73
Builder

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.

0 Karma

Esky73
Builder

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

DalJeanis
Legend

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

0 Karma

Esky73
Builder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...