topic Re: How write one search to find a percentage using fields from two reports with different statistics in the same summary index? in Splunk Search

How write one search to find a percentage using fields from two reports with different statistics in the same summary index?

vitorvmiguel — Wed, 12 Aug 2015 20:46:50 GMT

Hi guys,

I have a summarized index that contains two different reports, and these reports have statistical data with different parameters.

One report (report=MobilePJTotalClientesUnicos23hs) summarizes unique clients clientes_unicos, and the other report (report=ClientesImpactadosPorTransacaoMobilePJ_23h) summarizes impacted clients ClientesImpactados by program programa.

So I want to do a search to calculate one percentage of impacted clients by program that is as simple as eval percentual=ClientesImpactados/clientes_unicos, by program, but I can't figure out how to do that because one report statistic is by program and the other is not.

I'm posting one example of the search I thought would do the job, but the result I get is the image below.

index=sum_internet report=ClientesImpactadosPorTransacaoMobilePJ_23h OR report=MobilePJTotalClientesUnicos23hs | eval percentual=ClientesImpactados/clientes_unicos | table programa percentual

So sorry about my English, I hope someone can help me with that.

Rgs.,

Re: How write one search to find a percentage using fields from two reports with different statistics in the same summary index?

woodcock — Wed, 12 Aug 2015 21:35:47 GMT

Although I could show you how to do it, I won't do so because the result will be incorrect. Here is why. In one data set you have a sum indicating the number of unique users for that timespan. In the other data set, you have a sum of the number of unique users that were impacted by some problem with a program. Because you have done something like dc(usesr) by App, and you have lost the user details, you have no way of knowing whether user X has been counted in more than one App so it is NOT VALID to add those counts together to obtain "Total number of impacted users across all Apps" and if we cannot obtain this value, then we cannot generate your desired value.

Re: How write one search to find a percentage using fields from two reports with different statistics in the same summary index?

vitorvmiguel — Thu, 13 Aug 2015 12:25:14 GMT

I understand what you said, and it may seem weird but makes sense, i'll explain why. One report calculates the number of clients with problems in one particular program, and the other the total amount of clients, so if i want to calculate the percentage of clients with problems in one program, relative to the total of clients it makes sense.
So the problem is that the report with the total number of clients has only one result, its one constant that i wish to use to calculate the percentage, and the other report is clients over programs.

So now makes sense?

Thanks.

Re: How write one search to find a percentage using fields from two reports with different statistics in the same summary index?

woodcock — Thu, 13 Aug 2015 13:15:31 GMT

It only makes sense if this is true:

No user in the "error" file may ever be counted against more than 1 program.

Is this statement true or false? Obviously, if there is only 1 program in the dataset, then this must be true, but your picture clearly shows 4 programs.

Re: How write one search to find a percentage using fields from two reports with different statistics in the same summary index?

vitorvmiguel — Thu, 13 Aug 2015 13:36:52 GMT

It's not about make sense or not, and how to do that, what i'm trying to do makes sense to me and to everyone that works with me, so that's not the point.

Re: How write one search to find a percentage using fields from two reports with different statistics in the same summary index?

somesoni2 — Thu, 13 Aug 2015 15:25:52 GMT

Try something like this

 index=sum_internet report=ClientesImpactadosPorTransacaoMobilePJ_23h OR report=MobilePJTotalClientesUnicos23hs | table programa ClientesImpactados, clientes_unicos | eventstats values(clientes_unicos) as clientes_unicos | where programa=*| eval percentual=ClientesImpactados/clientes_unicos | table programa percentual

Re: How write one search to find a percentage using fields from two reports with different statistics in the same summary index?

woodcock — Thu, 13 Aug 2015 16:15:12 GMT

It is NOT valid to do sum on a dc value. It is not ethical to help somebody produce a result that I know (or have every reason to believe) is invalid.