@bowesmana    Thank you for your prompt reply. As you can see some rows have more than 1 data of AreaOIC, Biodiversity2, Land_Ownership, and PlanningCommitment. So my goal is: 1. to be able to filter one and only one data per row. (e.g. AreaOIC only) 2. Datacount is only counting that data and not the other data Not sure if that is possible. Let me know if you know how ... or what the issue is... ... View more

      | tstats values(Arcgis.email) as email, values(Arcgis.agency) as Agency, values(Arcgis.mapservice) as Data, count(Arcgis.mapservice) as Datacount, values(Arcgis.mapfolder) as Mapfolder from datamodel=Arcgis where (host=URASVR334) groupby _time Arcgis.email | search [tstats values(Eplanner.loginname) as email from datamodel=Eplanner | table email] NOT Mapfolder=*ONETOOL* NOT Mapfolder=*GEMMA* NOT Mapfolder=*Scenarios* NOT Mapfolder=*USDashboard* NOT Mapfolder=*EPAC* NOT Mapfolder=*CLI* NOT Mapfolder=*MP14* NOT Data=*_3414* | eval Email=upper(email) | append [| tstats values(Eplanner.email) as Email, values(Eplanner.agency) as Agency, values(Eplanner.layers) as Data, count(Eplanner.layers) as Datacount from datamodel=Eplanner groupby _time Eplanner.email | eval Email=upper(Email)] | append [| tstats values(Eplanner.email) as Email, values(Eplanner.agency) as Agency, values(Eplanner.typename) as Data, count(Eplanner.typename) as Datacount from datamodel=Eplanner groupby _time Eplanner.email | eval Email=upper(Email)] | lookup eplannerusers.csv "Login Name" as Email OUTPUT "Login Name" Date Group as Group Department Designation "Full Name" as Fullname | strcat Fullname " / " Department Name_Dept | search Agency="Potato Hero" Email=Potato@Hero.potato.sg Data="AreaOIC"​       _TIME Arcgis.email email Agency Data Datacount Mapfolder Date Department Designation Email Eplanner.email Fullname Group Login Name Name_Dept 2021-04-12 Potato@Hero.potato.sg Potato@Hero.potato.sg Potato Hero AreaOIC Biodiversity2 PlanningCommitment 62 Boundaries DevtControl Planning 23/11/2020 23:30 IT Programmer Potato@Hero.potato.sg   Tan Jason ITA Potato@Hero.potato.sg Tan Jason, ITA 2021-04-23 Potato@Hero.potato.sg Potato@Hero.potato.sg Potato Hero AreaOIC 122 Boundaries 23/11/2020 23:30 IT Programmer Potato@Hero.potato.sg   Tan Jason ITA Potato@Hero.potato.sg Tan Jason, ITA 2021-05-04 Potato@Hero.potato.sg Potato@Hero.potato.sg Potato Hero AreaOIC Land_Ownership PlanningCommitment 52 Boundaries DevtControl Planning 23/11/2020 23:30 IT Programmer Potato@Hero.potato.sg   Tan Jason ITA Potato@Hero.potato.sg Tan Jason, ITA @bowesmana                              ... View more

@bowesmana    "so if you remove all of your search after that, how many rows (events) do you end up with and what are the values of Data?" -> do you mean remove anything after my search statement?  (e.g. stats values(Fullname)....)    what I get from it?   ... View more

@bowesmana  Hi thanks for your prompt reply, maybe my understanding of events might be wrong. Okay this is the result that I want to get...   FROM:   Email values(Fullname) values(Designation) values(Name_Dept) values(Group) values(Department) values(Agency) values(Mapfolder) values(Data) sum(Datacount) TAN@potato.hero.sg Tan Potato IT Analyst Tan Potato_IT ITA IT Potato Hero Boundaries DevtControl Planning AreaOIC Biodiversity2 Land_Ownership PlanningCommitment 236   TO:   Email values(Fullname) values(Designation) values(Name_Dept) values(Group) values(Department) values(Agency) values(Mapfolder) values(Data) sum(Datacount) TAN@potato.hero.sg Tan Potato IT Analyst Tan Potato_IT ITA IT Potato Hero Boundaries DevtControl Planning AreaOIC 236   Do look at the values in Red               ... View more

1. | tstats values(Arcgis.email) as email, values(Arcgis.agency) as Agency, values(Arcgis.mapservice) as Data, count(Arcgis.mapservice) as Datacount, values(Arcgis.mapfolder) as Mapfolder from datamodel=Arcgis where (host=URASVR334) groupby _time Arcgis.email | search [tstats values(Eplanner.loginname) as email from datamodel=Eplanner | table email] NOT Mapfolder=*ONETOOL* NOT Mapfolder=*GEMMA* NOT Mapfolder=*Scenarios* NOT Mapfolder=*USDashboard* NOT Mapfolder=*EPAC* NOT Mapfolder=*CLI* NOT Mapfolder=*MP14* NOT Data=*_3414* | eval Email=upper(email) | append [| tstats values(Eplanner.email) as Email, values(Eplanner.agency) as Agency, values(Eplanner.layers) as Data, count(Eplanner.layers) as Datacount from datamodel=Eplanner groupby _time Eplanner.email | eval Email=upper(Email)] | append [| tstats values(Eplanner.email) as Email, values(Eplanner.agency) as Agency, values(Eplanner.typename) as Data, count(Eplanner.typename) as Datacount from datamodel=Eplanner groupby _time Eplanner.email | eval Email=upper(Email)] | lookup eplannerusers.csv "Login Name" as Email OUTPUT "Login Name" Date Group as Group Department Designation "Full Name" as Fullname | strcat Fullname " / " Department Name_Dept| search Agency=URA Email=TAN@potato.hero.sg Data=AreaOIC | stats values(Fullname), values(Designation), values(Name_Dept), values(Group), values(Department), values(Agency), values(Mapfolder), values(Data), sum(Datacount) by Email | where !isnull(mvfind(Data, "AreaOIC")) RESULTS: No results found.     2. | tstats values(Arcgis.email) as email, values(Arcgis.agency) as Agency, values(Arcgis.mapservice) as Data, count(Arcgis.mapservice) as Datacount, values(Arcgis.mapfolder) as Mapfolder from datamodel=Arcgis where (host=URASVR334) groupby _time Arcgis.email | search [tstats values(Eplanner.loginname) as email from datamodel=Eplanner | table email] NOT Mapfolder=*ONETOOL* NOT Mapfolder=*GEMMA* NOT Mapfolder=*Scenarios* NOT Mapfolder=*USDashboard* NOT Mapfolder=*EPAC* NOT Mapfolder=*CLI* NOT Mapfolder=*MP14* NOT Data=*_3414* | eval Email=upper(email) | append [| tstats values(Eplanner.email) as Email, values(Eplanner.agency) as Agency, values(Eplanner.layers) as Data, count(Eplanner.layers) as Datacount from datamodel=Eplanner groupby _time Eplanner.email | eval Email=upper(Email)] | append [| tstats values(Eplanner.email) as Email, values(Eplanner.agency) as Agency, values(Eplanner.typename) as Data, count(Eplanner.typename) as Datacount from datamodel=Eplanner groupby _time Eplanner.email | eval Email=upper(Email)] | lookup eplannerusers.csv "Login Name" as Email OUTPUT "Login Name" Date Group as Group Department Designation "Full Name" as Fullname | strcat Fullname " / " Department Name_Dept | search Agency=Potato Hero Email=TAN@potato.hero.sg Data=AreaOIC | where !isnull(mvfind(Data, "AreaOIC")) | stats values(Fullname), values(Designation), values(Name_Dept), values(Group), values(Department), values(Agency), values(Mapfolder), values(Data), sum(Datacount) by Email RESULTS:   Email values(Fullname) values(Designation) values(Name_Dept) values(Group) values(Department) values(Agency) values(Mapfolder) values(Data) sum(Datacount) TAN@potato.hero.sg Tan Potato IT Analyst Tan Potato_IT ITA IT Potato Hero Boundaries DevtControl Planning AreaOIC Biodiversity2 Land_Ownership PlanningCommitment 236   3. | tstats values(Arcgis.email) as email, values(Arcgis.agency) as Agency, values(Arcgis.mapservice) as Data, count(Arcgis.mapservice) as Datacount, values(Arcgis.mapfolder) as Mapfolder from datamodel=Arcgis where (host=URASVR334) groupby _time Arcgis.email | search [tstats values(Eplanner.loginname) as email from datamodel=Eplanner | table email] NOT Mapfolder=*ONETOOL* NOT Mapfolder=*GEMMA* NOT Mapfolder=*Scenarios* NOT Mapfolder=*USDashboard* NOT Mapfolder=*EPAC* NOT Mapfolder=*CLI* NOT Mapfolder=*MP14* NOT Data=*_3414* | eval Email=upper(email) | append [| tstats values(Eplanner.email) as Email, values(Eplanner.agency) as Agency, values(Eplanner.layers) as Data, count(Eplanner.layers) as Datacount from datamodel=Eplanner groupby _time Eplanner.email | eval Email=upper(Email)] | append [| tstats values(Eplanner.email) as Email, values(Eplanner.agency) as Agency, values(Eplanner.typename) as Data, count(Eplanner.typename) as Datacount from datamodel=Eplanner groupby _time Eplanner.email | eval Email=upper(Email)] | lookup eplannerusers.csv "Login Name" as Email OUTPUT "Login Name" Date Group as Group Department Designation "Full Name" as Fullname | strcat Fullname " / " Department Name_Dept | where !isnull(mvfind(Data, "AreaOIC")) | search Agency=URA Email=Jamie_TAN@ura.gov.sg Data=AreaOIC | stats values(Fullname), values(Designation), values(Name_Dept), values(Group), values(Department), values(Agency), values(Mapfolder), values(Data), sum(Datacount) by Email RESULTS: Same as 2. @bowesmana @abowesman  ... View more

@bowesmana  Ah thanks for explaining. I have tried the wrapping before and it doesn't work. I have tried putting the statement "| where !isnull(mvfind(Data, "AreaOIC"))" in 3 different places to try it out but none worked: 1. before search statement 2. before stats statement 3. after stats statement ... View more

@abowesman  Thank you for your reply, I have tried: | where !isnull(mvfind(Data, "AreaOIC")) however, it doesn't work, it still shows the same result.   May I know how do I do "wrap it with wildcard characters."? ... View more