Getting Data In

How to sort JSON Array using raw data?

bora9
Explorer

Hello I've been trying to chart/table the following search but I keep getting the wrong sorting for my array.

My search :

source="rest://Energy Lifetime" 
| spath input=_raw output=production path=production{} 
| head 1
| stats latest(production) by production 
| table meter_start_date, production

My raw:

{"system_id":0023251,"start_date":"2017-09-14","production":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,10166,8951,8954,5360,9469,9871,9045,8360,5584,9431,9523,9307,8996,6431,4364,9331,5930,4394,9321,8785,8235,4023,9417,8006,4370,8229,2764,9600,9337,4105,6717,8890,9576,9483,10282,9552,4689,6193,9013,3702,8725,3724,9574,3831,7774,10389,3462,9767,10800,9055,4421,7062,2751,10919,11605,9039,7970,5843,10982,10377,11656,3383,8779,10129,12253,11515,5248,11817,10511,13125,10561,7534,246,1601,1460,11066,8822,10814,15167,15136,10716,1912,1235,5973,11124,7391,16624,16954],"meter_start_date":"2017-09-14","meta":{"status":"normal","last_report_at":1519660861,"last_energy_at":1519660800,"operational_at":1505428786}}

The sorting Im expecting is exactly how it shows on the raw for production (0,
0,
0,
... ,
16624,
16954)

but I keep getting the following sorting (0,
10129,
10166,
10282,
...,
9576,
9600,
9767,
9871
)

Can anybody please help as I have been trying to do this without any luck.

UPDATE: Now I want to be able to give the correct dates to the table but since the raw data only includes the start date I would have to generate all dates in a consecutive order. I have tried the following:

| gentimes start=9/14/2017
| eval Date=strftime(endtime,"%m/%d/%Y") 
| table Date

Which will give me a consecutive dates from 9/14/2017 until now but I can't seem to manage to replace meter_start_date with these new values

what I tried so far

source="rest://Energy Lifetime" 
| spath input=_raw output=production path=production{} 
| head 1
| table Date production 
| join [| gentimes start=9/14/2017
| eval Date=strftime(endtime,"%m/%d/%Y") 
| fields Date
] 
| mvexpand production
| where production > 0

This is yielding all days to be the same. I think it is due to the "head 1" but not quite sure how to integrate it in order to keep the same order

FINAL ANSWER:

Managed to get it to work now by doing the following:

source="rest://Energy Lifetime" 
| spath input=_raw output=production path=production{} 
| head 1
| mvexpand production
| table Date production 
| appendcols [| gentimes start=9/14/2017
| eval Date=strftime(endtime,"%m/%d/%Y") 
| fields Date
] 
| where production > 0

This will plot all the dates ranging from 9/14/2017 until today along with the production generated each day.

Tags (4)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

source="rest://Energy Lifetime" 
 | spath   | head 1 | rename production{} as production
| table meter_start_date production | mvexpand production

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

source="rest://Energy Lifetime" 
 | spath   | head 1 | rename production{} as production
| table meter_start_date production | mvexpand production
0 Karma

bora9
Explorer

That search ended up providing the same answer twice in the correct order. Ex. (0,
0,
0,
... ,
16624,
16954)

then repeated itself again.

Your search guided me towards the correct idea and ended up using the following search to chart correctly:

source="rest://Energy Lifetime"
| spath input=_raw output=production path=production{}
| head 1
| table meter_start_date production
| mvexpand production

Thank you very much for the help!

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