Convert table generated by JSON into a timeline

[topherbirth]

I have a service that is dropping a json object every 5 minutes. These objects contain multiple KeyValuePair Categories with multiple KVP Properies within them. If I use the following query:

index= ****Query ****
| spath input=Properties.Data path=Items{} output=Items
| stats count by Items
| spath input=Items path=Props{} output=Props
| mvexpand Props
| spath input=Props
| spath input=Items
| fields - Items count Props*
| where CN="ClientId" AND PN="Client_authentication_success"

Which generates a table:
CN | CV | PN | PV
(CategoryName | CategoryValue | PropertyName | PropertyValue)

The problem is that I cannot seem to generate a timeline from that data. I think this is because its pulling ALL the data from the entire duration and none of it contains any time information for each individual log they belong to. So I may need to inject the time information early on before it aggregates all the data into one report. 

I'm Assuming the table would need to look more like:
_time | CN | CV | PN | PV

From there I'm assuming it would be something like:
| timechart span=5m sum(PV) by CV where sum in top10
To get a timeline going.

Here is an example of one record:
{"Items":[{"CN":"ClientId","CV":"ABC0001","Props":[{"PN":"Client_authentication_success","PV":10}]},{"CN":"ClientId","CV":"CDE0001","Props":[{"PN":"Client_authentication_success","PV":754}]},{"CN":"ClientId","CV":"ABC0002","Props":[{"PN":"Client_authentication_success","PV":33}]}]}

[topherbirth · Accepted solution]

I managed to get a working query:

| spath input=Properties.Data path=Items{} output=Items
| mvexpand Items
| rename Items as _raw
| spath path=Props{} output=ThePs
| mvexpand ThePs
| kv
| spath input=ThePs path=PN output=PN
| spath input=ThePs path=PV output=PV
| table _time, CN, CV, PN, PV
| timechart span=5m sum(PV) by CV where sum in top10

[isoutamo]

Hi

can you try if you adding replace your stats with

| stats values(_time) as _time count by Items

on your query?

r. Ismo