For each group of UIDs, how do I edit my search to filter out values from ColumnB that are not present in ColumnA?

[metersk]

earliest=-60d@d latest=-0d@d msg=login_daily | eval time=strftime(_time, "%m/%d/%y") | where cadt>1421366400 |stats count by uid time platform plats fplat is_p gender

The above query gives me data presented like so:

+-----+---------+----------+-------+-------+------+-------+
| uid |  time   | platform | plats | fplat | is_p | count |
+-----+---------+----------+-------+-------+------+-------+
|   1 | 1/16/15 |        2 | 0,2   |     0 |      |     1 |
|   1 | 1/17/15 |        2 | 0,2   |     0 |      |     1 |
|   1 | 1/18/15 |        2 | 0,2   |     0 |      |     1 |
|   1 | 1/19/15 |        2 | 0,2   |     0 |      |     1 |
|   1 | 1/20/15 |        2 | 0,2   |     0 |      |     1 |
|   1 | 1/21/15 |        2 | 0,2   |     0 |      |     1 |
|   2 | 2/8/15  |        4 | 2,0,4 |     2 |      |     1 |
|   3 | 1/29/15 |        1 | 0,1,2 |     1 |      |     1 |
|   3 | 1/30/15 |        1 | 0,1,2 |     1 |    1 |     1 |
|   3 | 1/31/15 |        1 | 0,1,2 |     1 |    1 |     1 |
|   3 | 2/2/15  |        1 | 0,1,2 |     1 |    1 |     1 |
|   3 | 2/3/15  |        1 | 0,1,2 |     1 |    1 |     1 |
|   4 | 1/28/15 |        2 | 2     |     2 |      |     1 |
|   4 | 1/29/15 |        2 | 2     |     2 |      |     1 |
|   5 | 1/18/15 |        4 | 2,4   |     2 |      |     1 |
|   6 | 1/30/15 |        2 | 2     |     2 |      |     1 |
|   7 | 2/2/15  |        0 | 2,0   |     2 |      |     1 |
|   8 | 3/9/15  |        1 | 2,1   |     2 |      |     1 |
|   9 | 2/28/15 |        1 | 1,2   |     2 |      |     1 |
|   9 | 3/2/15  |        1 | 1,2   |     2 |      |     1 |
|   9 | 3/5/15  |        1 | 1,2   |     2 |      |     1 |
|   9 | 3/10/15 |        1 | 1,2   |     2 |      |     1 |
|   9 | 3/11/15 |        1 | 1,2   |     2 |      |     1 |
|   9 | 3/15/15 |        1 | 1,2   |     2 |      |     1 |
|  10 | 3/15/15 |        2 | 2     |     2 |      |     1 |
|  11 | 3/16/15 |        0 | 2,0   |     2 |      |     1 |
|  12 | 3/12/15 |        4 | 2,4   |     2 |      |     1 |
+-----+---------+----------+-------+-------+------+-------+

What I would like to do is this:
For each grouping of UIDs, if they do not have a platform of 2 in any instance, remove it from their plats column. For example, UID 3 does not have 2 in any of its platform instances. I would like to strip out 2 from their plats data.

Is this possible? Please let me know if I can make this more clear.

[acharlieh · Accepted solution]

So for your case, where you exactly want to remove 2 from the field plats, if 2 is not contained in the set of platforms for a given uid.

<your query> | eventstats values(platform) as platforms by uid | eval has2=mvcount(mvfilter(platforms=2)) | eval newplats=split(plats,",") | eval newplats=mvjoin(if(has2>0,newplats,mvfilter(newplats!=2)),",") 

eventstats adds a new column to every event containing all unique platforms for the uid. We use eval to see if the number 2 is among them. We turn the plats field into a multivalued field. Finally, if we do not have the number 2 for our uid, we remove the number 2 from the multivalued field, then join the values back together.

The last two steps I used newplats so that way you could compare plats and newplats side by side, but you could change newplats to just plats everywhere to throw away the old value.

[vganjare]

HI,

In above case, the group 3 has platform 1 only. So, do you want to retain only 1 in plats data?

Thanks,
Vishal

[metersk]

For group 3 I would like to retain only 0 and 1. Another example, group 9 should only retain plats 1

Thanks!