Timechart query with multiple values after "by"

[sawgata12345]

I have similar json input as below, every minute similar blocks of data is send to index.

I am plotting timechart but its constraint is we can provide only one field after "by" as below:
index="test19" | eval res = readio / 1 | timechart span=60 avg(res) as AVG usenull=f by sid

but i need value of readio to be averaged for a unique combination of (did and lun) or (sid,lun) or (sid,did)
if suppose i take a combination of s1,d1 there are 5 rows for which avg to be calculated
similarly for s2,d1 has 4 rows
so the timechart should be showing avg of "readio" for s1-d1(avg of 5 values),s2-d1(avg of 4 values),s3-d1(avg for 4 values),s1-d2(avg of 2 values),s1-d3(avg of 3 values) as series

--event 1 - all records going at once
{"sid": "s1", "did": "d1","lun": "l1","readio":"10","iocompletetime":"2","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d1","lun": "l2","readio":"50","iocompletetime":"10","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d1","lun": "l3","readio":"5","iocompletetime":"2","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d1","lun": "l4","readio":"100","iocompletetime":"20","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d1","lun": "l5","readio":"1","iocompletetime":"1","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d1","lun": "l6","readio":"5","iocompletetime":"2","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s2", "did": "d1","lun": "l1","readio":"15","iocompletetime":"2","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s2", "did": "d1","lun": "l2","readio":"10","iocompletetime":"2","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s2", "did": "d1","lun": "l3","readio":"10","iocompletetime":"1","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s2", "did": "d1","lun": "l4","readio":"12","iocompletetime":"2","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s3", "did": "d1","lun": "l6","readio":"19","iocompletetime":"3","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s3", "did": "d1","lun": "l7","readio":"105","iocompletetime":"25","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s3", "did": "d1","lun": "l8","readio":"66","iocompletetime":"15","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s3", "did": "d1","lun": "l9","readio":"80","iocompletetime":"20","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d2","lun": "l1","readio":"200","iocompletetime":"40","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d2","lun": "l2","readio":"20","iocompletetime":"5","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d3","lun": "l3","readio":"25","iocompletetime":"5","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d3","lun": "l4","readio":"50","iocompletetime":"8","Timestamp": "2018-03-24 06:12:53"}
{"sid": "s1", "did": "d3","lun": "l5","readio":"70","iocompletetime":"6","Timestamp": "2018-03-24 06:12:53"}

--event 2 all records going at once
{"sid": "s1", "did": "d1","lun": "l1","readio":"10","iocompletetime":"2","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d1","lun": "l2","readio":"50","iocompletetime":"5","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d1","lun": "l3","readio":"5","iocompletetime":"1","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d1","lun": "l4","readio":"100","iocompletetime":"30","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d1","lun": "l5","readio":"1","iocompletetime":"1","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d1","lun": "l6","readio":"5","iocompletetime":"2","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s2", "did": "d1","lun": "l1","readio":"15","iocompletetime":"4","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s2", "did": "d1","lun": "l2","readio":"10","iocompletetime":"2","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s2", "did": "d1","lun": "l3","readio":"10","iocompletetime":"2","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s2", "did": "d1","lun": "l4","readio":"12","iocompletetime":"3","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s3", "did": "d1","lun": "l6","readio":"19","iocompletetime":"5","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s3", "did": "d1","lun": "l7","readio":"105","iocompletetime":"20","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s3", "did": "d1","lun": "l8","readio":"66","iocompletetime":"5","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s3", "did": "d1","lun": "l9","readio":"80","iocompletetime":"6","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d2","lun": "l1","readio":"200","iocompletetime":"40","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d2","lun": "l2","readio":"20","iocompletetime":"5","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d3","lun": "l3","readio":"25","iocompletetime":"3","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d3","lun": "l4","readio":"50","iocompletetime":"6","Timestamp": "2018-03-24 06:13:23"}
{"sid": "s1", "did": "d3","lun": "l5","readio":"70","iocompletetime":"8","Timestamp": "2018-03-24 06:13:23"}

[TISKAR]

Hello,

Try this

| makeresults 
| eval field="{\"sid\": \"s1\", \"did\": \"d1\",\"lun\": \"l1\",\"readio\":\"10\",\"iocompletetime\":\"2\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d1\",\"lun\": \"l2\",\"readio\":\"50\",\"iocompletetime\":\"10\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d1\",\"lun\": \"l3\",\"readio\":\"5\",\"iocompletetime\":\"2\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d1\",\"lun\": \"l4\",\"readio\":\"100\",\"iocompletetime\":\"20\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d1\",\"lun\": \"l5\",\"readio\":\"1\",\"iocompletetime\":\"1\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d1\",\"lun\": \"l6\",\"readio\":\"5\",\"iocompletetime\":\"2\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s2\", \"did\": \"d1\",\"lun\": \"l1\",\"readio\":\"15\",\"iocompletetime\":\"2\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s2\", \"did\": \"d1\",\"lun\": \"l2\",\"readio\":\"10\",\"iocompletetime\":\"2\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s2\", \"did\": \"d1\",\"lun\": \"l3\",\"readio\":\"10\",\"iocompletetime\":\"1\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s2\", \"did\": \"d1\",\"lun\": \"l4\",\"readio\":\"12\",\"iocompletetime\":\"2\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s3\", \"did\": \"d1\",\"lun\": \"l6\",\"readio\":\"19\",\"iocompletetime\":\"3\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s3\", \"did\": \"d1\",\"lun\": \"l7\",\"readio\":\"105\",\"iocompletetime\":\"25\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s3\", \"did\": \"d1\",\"lun\": \"l8\",\"readio\":\"66\",\"iocompletetime\":\"15\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s3\", \"did\": \"d1\",\"lun\": \"l9\",\"readio\":\"80\",\"iocompletetime\":\"20\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d2\",\"lun\": \"l1\",\"readio\":\"200\",\"iocompletetime\":\"40\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d2\",\"lun\": \"l2\",\"readio\":\"20\",\"iocompletetime\":\"5\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d3\",\"lun\": \"l3\",\"readio\":\"25\",\"iocompletetime\":\"5\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d3\",\"lun\": \"l4\",\"readio\":\"50\",\"iocompletetime\":\"8\",\"Timestamp\": \"2018-03-24 06:12:53\"};{\"sid\": \"s1\", \"did\": \"d3\",\"lun\": \"l5\",\"readio\":\"70\",\"iocompletetime\":\"6\",\"Timestamp\": \"2018-03-24 06:12:53\"}"
| makemv field delim=";"
| mvexpand field
| rex field=field "\"sid\":\s+\"(?<sid>[^,]*)\",\s+\"did\":\s+\"(?<did>[^,]*)\",\"lun\":\s+\"(?<lun>[^,]*)\""
| rex field=field "\"Timestamp\":\"(?<_time>[^}]*)"
| rex field=field "\"readio\":\"(?<readio>[^,]*)\""
| fields sid did lun readio
| eval coutBy=sid." ".did." ".lun
| eval res = readio / 1
| timechart  avg(res) By combination

[HeinzWaescher · Accepted solution]

Have you tried out to merge the parameters to unique combinations before the timechart?

 | eval combination=sid."-".did."-".lun
 | eval res = readio / 1
 | timechart span=60min avg(res) By combination

[akocak]

I was about to write exactly the same. Too late 🙂

[sawgata12345]

Hi,
Addition to the above question:
eval combination=sid."-".did."-".lun

for this sid,did and lun now i have three dropdowns where at page load first the sid gets filled up. Then upon selection of one sid , corresponding dids gets filled up, and selecting a did finally the lun dropdown gets filled up.
This is done.
The thing is if i do not select any of these the query in a panel should show result for all as below:
index="test19"|fields sid,did,lun,readio|eval combination=sid."-".did."-".lun | eval res = readio / 1 | timechart span=60 avg(res) as AVG usenull=f by combination

But if I select specific values of sid,did,lun from dropdowns then query should show only for those selected :
index="test19" sid=$sids$ did=$dids$ lun=$luns$|fields sid,did,lun,readio|eval combination=sid."-".did."-".lun | eval res = readio / 1 | timechart span=60 avg(res) as AVG usenull=f by combination

I have used "Dynamic Options"->search string to prefill the dropdowns. Now with this the "Token Options"->Default gets disabled, I cant add a default value so that if dropdown is not selected it takes for eg "" for lun=$$.

Also i need to change the eval part : eval combination=sid."-".did."-".lun
i.e if lun is not select in dropdown means all luns so it should be like
eval combination=sid."-".did
and not
eval combination=sid."-".did."-".lun

[woodcock]

That is the standard approach.