How to remove event/(s) from the values(Data) field?

[PotatoHero]

Hi I would like to remove some Data from my search (only want AreaOIC), however, I tried to do Data = AreaOIC or Data != XXXXX (*xxxx = field I would like to exclude), it still include other events where I do not want. How should I go about doing this?

| 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 as Group Department Designation "Full Name" as Fullname | strcat Fullname " / " Department Name_Dept

| search Agency=PotatoEdu Email=Potatohero@potato.edu.SG Group = PotatoEdu Data = AreaOIC

| stats values(Fullname), values(Designatio...

Email	values(Fullname)	values(Designation)	values(Name_Dept)	values(Group)	values(Department)	values(Agency)	values(Mapfolder)	values(Data)	sum(Datacount)
Potatohero@potato.edu.SG	Ken Do	Programmer	Ken Do	IT	IT	PotatoEDU	Boundaries DevtControl Planning	AreaOIC Land_Ownership PlanningCommitment	52

[abowesman]

Your values(Data) fields shows that the text 'AreaOIC' is either part of a longer string or one of multiple values of that field.

If it is a part of a longer string, then wrap it with wildcard characters. If it one of multiple values, then use the syntax

| where !isnull(mvfind(Data, "AreaOIC"))

after your final search command.

TIP: When doing stats values(Data), use a field renaming command also, so you end up with a useful field name after the stats, i.e.

| stats values(Data) as Data

as is done in your main search body.

[PotatoHero]

@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."?

[bowesmana]

Wrap with wildcard characters means replace your current statement, which does

| search .... Data = AreaOIC

with

| search .... Data = "*AreaOIC*"

Where did you place the mvfind() statement. Did you place it before the stats and if so, can you can an example of the data it found that it should have not found

[PotatoHero]

@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

[bowesmana]

@PotatoHero 

Can you post some examples, which show the query and the results. It's hard to diagnose the issue without being able to see what you are or are not getting.

[PotatoHero]

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 

[bowesmana]

Result 1 is correct, because you do not have a field called Data. I mentioned that you should rename the values(Data) as Data in your stats, otherwise you no longer have a field called data.

Result 2 is showing you a result where the Data field contains AreaOIC - is that wrong? You only are showing a single result row - if it is wrong, then what result are you expecting?

Result 3 same reply as for 2.

When you originally said you want to remove events, can you explain what events are present that should not be.

[PotatoHero]

@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

[bowesmana]

OK, so this is your search so far... (please use the code sample option to paste in your search code </>)

| 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

You Data field comes from 

First line of search = values(Arcgis.mapservice) as Data

First append line = values(Eplanner.layers) as Data

Second append line = values(Eplanner.typename) as Data

so, you have types of 'Data' that may appear. Consider now

| search Agency=Potato Hero Email=TAN@potato.hero.sg Data=AreaOIC

This line will, in theory only include Data values that are AreaOIC

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?

Note that your search command is most likely bad, in that I suspect you should really be quoting

Agency="Potato Hero", otherwise you are searching for Agency=Potato and then you are searching for the word Hero 

Please share what results you get from the above and what the real values of Data in each event are

[PotatoHero]

@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?

[bowesmana]

yes

[PotatoHero]

| 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 

[bowesmana]

OK, so now it's clearer.  You have 3 records, and each of these has at least Data containing AreaOIC and these cover different data counts and different dates.

So, which of these 3 rows should be included or excluded in the total created by the final stats statement.

Note that Datacount is coming from all 4 Data values across your results, 

AreaOIC
Biodiversity2
Land_Ownership
PlanningCommitment

so what exactly do you want to show in your final result from that stats of those 3 rows?

[PotatoHero]

@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...

[bowesmana]

So, you need to get back to basics. This is your most recent search:

| 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"

 The field Data comes from the values of 3 different fields from 3 different tstats statements. You can't collect all the different fields then expect to somehow filter out the unwanted ones at the end of the search,  particularly as you are combining the counts of these Data fields at each stage, so it's impossible to undo that further down your search.

You will need to only search for your wanted Data field in each of the tstats statements, so you need to add the X="AreaOIC" filter criteria into each of your tstats statements, where you are collecting data, e.g.

tstats ... values(Arcgis.mapservice) as Data .... where ... Arcgis.mapservice="AreaOIC"

tstats ... values(Eplanner.layers) as Data .... where ... Eplanner.layers="AreaOIC"

tstats ... values(Eplanner.typename) as Data .... where ... Eplanner.typename="AreaOIC"