I am trying ways to avoid join function. I am combining two different sourcetype for all time duration. since i am using all time i cannot use Join due to slow performance and limation in numbers of rows to join. index=test sourcetype=a or sourcetype=b latest=now now lets assume a1 is the field in sourcetype a which is equal to b1 in the sourcetype b and both the sourcetype has over a 2 lakh rows my next step is doing a colaese function to link this | eval test=coalesce(a1,b1) now to make things fast i am doing a stats | stats list(a2),list(a3),list(a4),list(a5),list(b1),list(b2) by test now my stats will reduce values to 1 lakh, also i am using lust command because some fields in sourcetype a will be tagged to multiple fields in sourcetype6 somethg like a1=b1----> (b1.1,b1.2,b1.3) now here since i am having more than 1 lakh rows my mvexpand failes i am more concerned about multi value columns with multi value fields in other words how to extract indivual rows from a multi value columns multi value row kind of event of a stats command without using mvindex command ... View more

this is a kind of restricted data where i cannot share but i can share you a part of logic which i have used i have used the below spl to extract set of 8 fields from a statistics table shared in teh case . of course in the case i have given only four fields. | eval zipped = mvzip(tasknumber , taskstate, "###") | eval zipped = mvzip(zipped, taskassignmentgroup, "###") | eval zipped = mvzip(zipped, taskcreateddate, "###") | eval zipped = mvzip(zipped, taskassigned, "###") | eval zipped = mvzip(zipped, taskreassignmentcount, "###") | eval zipped = mvzip(zipped, tasklastupdated, "###") | eval zipped = mvzip(zipped, taskdescription, "###") | eval zipped= mvzip(zipped,projectidentifier, "###") | eval zipped= mvzip(zipped,requestservice, "###") | mvexpand zipped | makemv delim="###" zipped | eval finaltasknumber = mvindex(zipped, 0) | eval finaltaskstate = mvindex(zipped, 1) | eval finaltaskassignmentgroup = mvindex(zipped, 2) | eval finaltaskcreateddate = mvindex(zipped, 3) | eval finaltaskassigned = mvindex(zipped, 4) | eval finaltaskreassignmentcount = mvindex(zipped, 5) | eval finaltasklastupdated = mvindex(zipped, 6) | eval finaltaskdescription = mvindex(zipped, 7) | eval finalrequestservice = mvindex(requestservice, 0) | eval finalprojectidentifier = mvindex(projectidentifier, 0) I have to go through this step especially for rows which have multi values. All i need is just skip mvexpand command and go for a different logic , because of bulk data. ... View more

many thanks for your prompt response a left type left join seems to produce good results :-). ... View more

this is a table command indeed and not a table :-). ... View more

many thanks for your timely response.I managed to design the query with type left join and it seemed to work :-). ... View more

Hi thanks for this. I should display the result in a table something like a table with all the fields combined to gether something like table A B C D E F. how to do this. ... View more

Hi thanks for this. I should display the result in a table something like a table with all the fields combined to gether something like table A B C D E F. how to do this. ... View more