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
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
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
Thank you so much, its working
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
What happens if you add:
| appendpipe [| stats sum(ProCount) as Total by project] | eval Perc=ProCount/project*100 | search Perc>5 UT="No"
Thank you for your response where I have to add this query after the subsearch or end of the search, Pl suggest
Thank you
I copied your query after subsearch and end of the subsearch but not getting any results. pl help me
Thanks
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?
Hi,
Thank you for your response
I sent the sample data can you help me
Regards
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
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