Splunk Search

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

PotatoHero
Loves-to-Learn Lots

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

Labels (4)
0 Karma

abowesman
Explorer

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.

0 Karma

PotatoHero
Loves-to-Learn Lots

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

0 Karma

bowesmana
Champion

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

0 Karma

PotatoHero
Loves-to-Learn Lots

@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


0 Karma

bowesmana
Champion

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

0 Karma

PotatoHero
Loves-to-Learn Lots



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:

 

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 

0 Karma

bowesmana
Champion

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.

 

0 Karma

PotatoHero
Loves-to-Learn Lots

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

 

Emailvalues(Fullname)values(Designation)values(Name_Dept)values(Group)values(Department)values(Agency)values(Mapfolder)values(Data)sum(Datacount)
TAN@potato.hero.sgTan PotatoIT AnalystTan Potato_ITITAITPotato HeroBoundaries
DevtControl
Planning
AreaOIC
Biodiversity2
Land_Ownership
PlanningCommitment
236

 

TO:

 

Emailvalues(Fullname)values(Designation)values(Name_Dept)values(Group)values(Department)values(Agency)values(Mapfolder)values(Data)sum(Datacount)
TAN@potato.hero.sgTan PotatoIT AnalystTan Potato_ITITAITPotato HeroBoundaries
DevtControl
Planning
AreaOIC

236

 

Do look at the values in Red

 

 

 

 

 

 

 

0 Karma

bowesmana
Champion

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

 

0 Karma

PotatoHero
Loves-to-Learn Lots

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

 

0 Karma

bowesmana
Champion

yes

0 Karma

PotatoHero
Loves-to-Learn Lots

 

 

 

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

 

 

 

_TIMEArcgis.emailemailAgencyDataDatacountMapfolderDateDepartmentDesignationEmailEplanner.emailFullnameGroupLogin NameName_Dept
2021-04-12Potato@Hero.potato.sgPotato@Hero.potato.sgPotato HeroAreaOIC
Biodiversity2
PlanningCommitment
62Boundaries
DevtControl
Planning
23/11/2020
23:30
ITProgrammerPotato@Hero.potato.sg Tan JasonITAPotato@Hero.potato.sgTan Jason, ITA
2021-04-23Potato@Hero.potato.sgPotato@Hero.potato.sgPotato HeroAreaOIC122Boundaries23/11/2020
23:30
ITProgrammerPotato@Hero.potato.sg Tan JasonITAPotato@Hero.potato.sgTan Jason, ITA
2021-05-04Potato@Hero.potato.sgPotato@Hero.potato.sgPotato HeroAreaOIC
Land_Ownership
PlanningCommitment
52Boundaries
DevtControl
Planning
23/11/2020
23:30
ITProgrammerPotato@Hero.potato.sg Tan JasonITAPotato@Hero.potato.sgTan Jason, ITA

@bowesmana 

 

 

 

 

 

 

 

 

 

 

 

0 Karma

bowesmana
Champion

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?

 

0 Karma

PotatoHero
Loves-to-Learn Lots

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

0 Karma

bowesmana
Champion

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"

 

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!