Customised search

[Santosh2]

Query:

index=abc mal_code=xyz TERM(application) OR (TERM(status) TERM(success)) NOT (TERM(unauthorized) TERM(time) TERM(mostly)) site=SOC
|stats count by Srock
|stats sum(count) as Success
|appendcols
[search index=abc mal_code=xyz (TERM(unauthorized) TERM(time) TERM(mostly)) NOT (TERM(status) TERM(success)) site=SOC
|stats count by ID
|fields ID
|eval matchfield=ID
|join matchfield [search index=abc mal_code=xyz site=SOC "application"
|stats count by Srock
|fields Srock
|eval matchfield=Srock]
|stats count(matchfiled) as Failed]
|eval Total=Success+Failed
|eval SuccessRate=round(Success/Total*100,2)
|table *

From the above query i am getting data only for one site. but I want data for both sites SOC and BDC.
I tried giving  as site=* its not working

Any help would be appreciated.

[PickleRick]

The whole search is... strange to say the least.

You generate a single value (and do that in a strange way - by aggregating by a field and the  ignoring the split by that field and summing up everything). Then you use appendcols to add another value which is obtained by joining two data sets from the same index.

Very strange and possibly inefficient way.

Even if you split your data by site, there is no guarantee that both result sets joined by appendcols will have the same order of results (and appendcols doesn't care about any field matching or something like that so it's up to you to make sure both result sets are compatible).

Anyway, I suspect there might be a more elegant (and possibly more efficient) way to do the same.

Also remember that your search might be subject to subsearch limitations.

[ITWhisperer]

Your first search filters on SOC, your second search (first in appendcols) filters on SOC, your third search (first in join) filters on SOC - where would BDC come from?

Either remove the filter to get all sites or use (site=SOC OR site=BDC) as your filters

[Santosh2]

Hi @ITWhisperer , I tried giving as site=* it’s not working l am getting total value but I need values by site.

[ITWhisperer]

Try adding site to your by clauses on your stats commands

[Santosh2]

I tried in many ways but I am not getting expected output 

[ITWhisperer]

Exactly what have you tried?

[Santosh2]

Tried below query, but not getting values by site

index=abc mal_code=xyz TERM(application) OR (TERM(status) TERM(success)) NOT (TERM(unauthorized) TERM(time) TERM(mostly)) site=*
|stats count by Srock site
|stats sum(count) as Success
|appendcols
[search index=abc mal_code=xyz (TERM(unauthorized) TERM(time) TERM(mostly)) NOT (TERM(status) TERM(success)) site=*
|stats count by ID site
|fields ID site
|eval matchfield=ID
|join matchfield [search index=abc mal_code=xyz site=* "application"
|stats count by Srock site
|fields Srock site
|eval matchfield=Srock]
|stats count(matchfiled) as Failed]
|eval Total=Success+Failed
|eval SuccessRate=round(Success/Total*100,2)
|table *

[ITWhisperer]

Missing site from this line

|stats sum(count) as Success site

[Santosh2]

Hi @ITWhisperer , i tried adding the missing line, but i am not getting the results by site.

I think we need to do some changes in the query but i am not getting it. 
Can anyone help on this.

[ITWhisperer]

Sorry - typo on my part - you need it like this

|stats sum(count) as Success by site

You also need site on some of the other stats commands too