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 UID
s, 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.
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.
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.
HI,
In above case, the group 3 has platform 1 only. So, do you want to retain only 1 in plats data?
Thanks,
Vishal
For group 3 I would like to retain only 0 and 1. Another example, group 9 should only retain plats 1
Thanks!