topic Re: How to remove event/(s) from the values(Data) field? in Splunk Search

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

PotatoHero — Tue, 11 May 2021 03:22:02 GMT

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(Designation), values(Name_Dept), values(Group), values(Department), values(Agency), values(Mapfolder), values(Data), sum(Datacount) by Email

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

abowesman — Tue, 11 May 2021 04:21:57 GMT

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.

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

PotatoHero — Tue, 11 May 2021 06:44:47 GMT

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

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

bowesmana — Wed, 12 May 2021 00:56:50 GMT

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

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

PotatoHero — Wed, 12 May 2021 01:48:03 GMT

@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

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

bowesmana — Wed, 12 May 2021 02:30:41 GMT

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

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

PotatoHero — Wed, 12 May 2021 02:51:10 GMT

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.

| 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

| 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

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

bowesmana — Wed, 12 May 2021 04:31:08 GMT

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.

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

PotatoHero — Wed, 12 May 2021 04:36:36 GMT

@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

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

bowesmana — Wed, 12 May 2021 06:14:07 GMT

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

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

PotatoHero — Wed, 12 May 2021 06:21:10 GMT

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

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

bowesmana — Wed, 12 May 2021 07:17:16 GMT

yes

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

PotatoHero — Wed, 12 May 2021 07:52:19 GMT

| 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

AreaOIC
Biodiversity2
PlanningCommitment

Boundaries
DevtControl
Planning

23/11/2020
23:30

Programmer

Potato@Hero.potato.sg

Tan Jason

ITA

Potato@Hero.potato.sg

Tan Jason, ITA

2021-04-23

Potato@Hero.potato.sg

Potato Hero

AreaOIC

122

Boundaries

23/11/2020
23:30

Programmer

Potato@Hero.potato.sg

Tan Jason

ITA

Potato@Hero.potato.sg

Tan Jason, ITA

2021-05-04

Potato@Hero.potato.sg

Potato Hero

AreaOIC
Land_Ownership
PlanningCommitment

Boundaries
DevtControl
Planning

23/11/2020
23:30

Programmer

Potato@Hero.potato.sg

Tan Jason

ITA

Potato@Hero.potato.sg

Tan Jason, ITA

@bowesmana

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

bowesmana — Wed, 12 May 2021 22:48:12 GMT

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?

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

PotatoHero — Thu, 13 May 2021 07:48:16 GMT

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

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

bowesmana — Thu, 13 May 2021 22:01:18 GMT

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"