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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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