How to only return values from a search where the percentage is greater than 5%?

[Laya123]

hi,

my search is :

index=* sourcetype=ABC host=ABC c_met="GET" c_u_s="*mweb.dll*"  [search index=* sourcetype=ABC host=ABC c_met="GET" c_u_s="*mweb.dll*" | where isnull(kid) and isnotnull(project) | stats count(project) as PCount by project | table project]| eval Sam=if(isnull(s), "NONE",s) | eval UT=if(isnull(kid), "No", "Yes") | stats count(project) as ProCount by project Sam UT| table project ProCount Sam UT

the above search is giving output like:

project ProCount    Sam  UT
A            10     S1   No
A           200     S1  Yes
B            25     S2   No
B           100     S2  Yes
C             2     S3   No
C             1   None     No
C           150     S3  Yes
D             3     S3   No
D             2        S4    No
D           125        S3   Yes
E           125        S2     No
E            10        S2   Yes
F             3     S3   No
F             2        S4    No
F           125        S3   Yes
F            10     S4  Yes

but I dont want all projects. I want to only see projects where the Nos of UT is greater than 5%.

This means I want my output to only be projects B & E because only these 2 projects have greater than 5% of Nos for that project.

project ProCount    Sam  UT
B            25     S2   No
B           100     S2  Yes
E           125        S2     No
E            10        S2   Yes

Please help me do this

Thanks

[somesoni2 · Accepted solution]

Try this

 index=* sourcetype=ABC host=ABC c_met="GET" c_u_s="*mweb.dll*"  [search index=* sourcetype=ABC host=ABC c_met="GET" c_u_s="*mweb.dll*" | where isnull(kid) and isnotnull(project) | stats count(project) as PCount by project | table project]| eval Sam=if(isnull(s), "NONE",s) | eval UT=if(isnull(kid), "No", "Yes") | stats count(project) as ProCount by project Sam UT| table project ProCount Sam UT | eval NoCount=if(UT="No",ProCount,0) | eventstats sum(NoCount) as NoCount sum(ProCount) as Total by project | where NoCount>0.05*Total | fields - NoCount Total

[Laya123]

Thank you so much, its working

[Laya123]

hi,

sorry for the late response and thank you so much for your help

here is the raw sample data

sourcetype host c_met c_u_s kid project s
ABC ABC GET mweb.dll 0djtr1 A S1
ABC ABC GET mweb.dll 0djtr2 A S1
ABC ABC GET mweb.dll 0djtr3 A S1
ABC ABC GET mweb.dll 0djtr4 A S1
ABC ABC GET mweb.dll A S1
ABC ABC GET mweb.dll A S1
ABC ABC GET mweb.dll 0djtr5 A S2
ABC ABC GET mweb.dll 0djtr6 A S2
ABC ABC GET mweb.dll 0djtr7 A S2
ABC ABC GET mweb.dll 0djtr8 A S2
ABC ABC GET mweb.dll 0djtr9 A S2
ABC ABC GET mweb.dll 0djtr10 A S2
ABC ABC GET mweb.dll abcd1 B S1
ABC ABC GET mweb.dll abcd2 B S1
ABC ABC GET mweb.dll abcd3 B S1
ABC ABC GET mweb.dll abcd4 B S1
ABC ABC GET mweb.dll B S1
ABC ABC GET mweb.dll B S1
ABC ABC GET mweb.dll B S2
ABC ABC GET mweb.dll B

ABC ABC GET mweb.dll B

ABC ABC GET mweb.dll B S2
ABC ABC GET mweb.dll abcd9 B S2
ABC ABC GET mweb.dll abcd9 B S2
ABC ABC GET mweb.dll lkimn1 C S1
ABC ABC GET mweb.dll lkimn2 C S1
ABC ABC GET mweb.dll lkimn3 C S1
ABC ABC GET mweb.dll lkimn4 C S1
ABC ABC GET mweb.dll C S1
ABC ABC GET mweb.dll C S1
ABC ABC GET mweb.dll C

ABC ABC GET mweb.dll lkimn6 C S2
ABC ABC GET mweb.dll lkimn7 C S2
ABC ABC GET mweb.dll lkimn8 C S2
ABC ABC GET mweb.dll lkimn9 C S2
ABC ABC GET mweb.dll lkimn10 C S2

form above data for some projects there is no kid my query is giving correct results only but i am getting all projects, but i want no kid >5% projects . I want my output from the above data is

My query:
index=* sourcetype=ABC host=ABC c_met="GET" c_u_s="mweb.dll" [search index=* sourcetype=ABC host=ABC c_met="GET" c_u_s="mweb.dll" | where isnull(kid) and isnotnull(project) | stats count(project) as PCount by project | table project]| eval Sam=if(isnull(s), "NONE",s) | eval UT=if(isnull(kid), "No", "Yes") | stats count(project) as ProCount by project Sam UT| table project ProCount Sam UT

using the above query i am getting all projects but i want only B. why because total procount = 12 out of this 6 are not having kid means 50% not having kid. like this i want my output where not having kid>5% of projects

project ProCount Sam UT
B 4 S1 Yes
B 2 S1 No
B 2 S2 No
B 2 S2 Yes
B 2 NONE No

Thank you so much

Regards

[masonmorales]

What happens if you add:

| appendpipe [| stats sum(ProCount) as Total by project] | eval Perc=ProCount/project*100 | search Perc>5 UT="No"

[Laya123]

Thank you for your response where I have to add this query after the subsearch or end of the search, Pl suggest

Thank you

[Laya123]

I copied your query after subsearch and end of the subsearch but not getting any results. pl help me

Thanks

[masonmorales]

It's very difficult to write a search based on the results of an existing search. Would you be able to post some sample data that we can work with?

[Laya123]

Hi,
Thank you for your response

I sent the sample data can you help me

Regards

[stephane_cyrill]

Hi Laya123,
I think what you have to do is to filter your search with the where command.
I don't understand what you call the Nos of UT. but assuming that you can you "Nos of UT" and your five_percent , try something like this:

.......|eval "No of UT"=.....|eval five_percent=.......| table project ProCount Sam UT|where "Nos fo UT" > five_percent

[Laya123]

Hi,

Thanks for your immediate response, Nos of UT means in my example there is one 'UT' column in that Yes and No values are there. I want percentage only for 'No' means where ever projects is having more than 5% of No i want to display only those projects with the same number of columns.

Thank you so much