How do you manipulate table results from a combined results?

[mabinn]

Hi,

I am stuck trying to manipulate my table when using a subsearch. Please see below query.

search .... | stats count as completed, values(id) as completed_ids 
      | appendcols [search .... | stats count as pending, values(id) as pending_ids ]
| table completed, pending, completed_ids, pending_ids

How can I make a new column named "status" and group my completed and pending fields there? Please see photo attached. I have used transpose method, but I am not sure that is the best solution here. Thanks a lot for your help!

[adonio · Accepted solution]

hello there,
short answer will be to add this to your search:

| eval status = "completed;;;pending"
| makemv delim=";;;" status
| mvexpand status
| eval ids = if(status=="completed",completed_id,pending_id)
| eval count = if(status=="completed",completed,pending)
| table status ids count

i would like to expand it and assume that you have data with some way to distinct between "pending" and "completed"
if this is the case, you can replace "appending" and "subsearching" with conditional eval.
in the example below, these are values, but you can use the condition that match your data with any eval.
try and run this search anywhere:

| makeresults count=10
| eval random_2 = random()%2
| eval pend_or_comp = if(random_2==0,"pending","completed")
| eval random_15 = random()%15 + 1
| eval random_13 = random()%13 + 1
| eval id = random_13 * random_15
| table pend_or_comp id 
| rename COMMENT as "above generates fake data, below is the solution"
| chart  count(eval(pend_or_comp=="completed")) as completed count(eval(pend_or_comp=="pending")) as pending values(eval(if(pend_or_comp=="completed",id,null()))) as completed_id values(eval(if(pend_or_comp=="pending",id,null()))) as pending_id
| eval status = "completed;;;pending"
| makemv delim=";;;" status
| mvexpand status
| eval ids = if(status=="completed",completed_id,pending_id)
| eval count = if(status=="completed",completed,pending)
| table status ids count

screenshot:

hope it helps

[mabinn]

Thank you very much, this solved my problem!
I think I can logically visualize what this line did, but could you you please explain what happened in these lines? what exactly did the third argument inside the if block do? Is there any link you can share with me so I can read up on it?

| eval ids = if(status=="completed",completed_id,pending_id)
| eval count = if(status=="completed",completed,pending)

[adonio]

we created 4 fields: completed, pending, completed_id, pending_id
with the eval statement, we are creating a new field called status, with 2 values, "completed" and "pending"
when we expand we have all records on both values.
now we want the fields ids, and count, created and their values to be appropriately set.
the eval created the field, the if condition says, if the status (remember, all records are in both rows) is "completed" take the values from the completed_id field, other, take the values from pending_id.
same for the count field.
if it solved your question, kindly accept the answer so others will know it worked for you

hope it helps

[mabinn]

Thanks a lot!