Splunk Search
Highlighted

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

Path Finder
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.

Highlighted

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

Builder

HI,

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

Thanks,
Vishal

0 Karma
Highlighted

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

Path Finder

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

Thanks!

0 Karma
Highlighted

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

Influencer

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.

View solution in original post