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.
Give this a try
source="rest://Energy Lifetime"
| spath | head 1 | rename production{} as production
| table meter_start_date production | mvexpand production
Give this a try
source="rest://Energy Lifetime"
| spath | head 1 | rename production{} as production
| table meter_start_date production | mvexpand production
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!