topic Re: How to expand rows without mvexpand command in Splunk Search

How to expand rows without mvexpand command

vkrishnachand — Wed, 31 Jul 2019 10:33:10 GMT

I am basically dealing with huge set of records where i am ending in mvexpand memory limit error. I want to extract data from below table without using mvexpand command.
if you notice the below table i want to extract as separate fields for each column. you can omit sno , because that is just to show that this is a multi value multi column table. This is the output of a stats command.

kindly assist

sno tasknumber projectidentifier taskstate
1 tas123 Null open
tas456 Null closed
tas789 Null incomplete
ritm234 Null null

Re: How to expand rows without mvexpand command

kamlesh_vaghela — Wed, 31 Jul 2019 11:01:37 GMT

@vkrishnachand

Can you please share your existing search and sample events?

Re: How to expand rows without mvexpand command

vkrishnachand — Wed, 31 Jul 2019 12:20:00 GMT

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.

Re: How to expand rows without mvexpand command

richgalloway — Wed, 31 Jul 2019 12:51:42 GMT

What exactly are you trying to accomplish? There may be another solution that does not require such a large multi-value field.
Sample data may help. Anonymize it, if necessary.

Re: How to expand rows without mvexpand command

vkrishnachand — Wed, 31 Jul 2019 15:34:16 GMT

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

Re: How to expand rows without mvexpand command

jnudell_2 — Wed, 31 Jul 2019 16:31:19 GMT

Your explanation is too confusing. Try putting together a few examples of data rows, with sensitive information removed, along with your desired output.

Re: How to expand rows without mvexpand command

richgalloway — Wed, 31 Jul 2019 18:37:20 GMT

Avoiding join is good. Using All Time is bad. Are you sure it's necessary?

Your comment well explains why you chose the approach you did, but it's still not clear what the end goal is. What is to be learned from the final results?