topic Adding 2 searches together sounds easy in Splunk Search

Adding 2 searches together sounds easy

rob3770 — Thu, 01 Feb 2018 16:33:06 GMT

index=ABC source="ABC"   ServiceName=ABC  |  
stats distinct_count(CorrelationId) as TotalA | 
appendcols [search  "TokenStatus=*Success*" ServiceName=ABC | stats distinct_count(CorrelationId) as TotalSuccess ]| 
appendcols [search TokenSubStatus=*error* ServiceName=ABC | stats distinct_count(CorrelationId) as TotalFailure ]| 
eval Total=(TotalSuccess*100)/TotalA | fields Total

I have been given the above query to troubleshoot and i've already pulled my hair out.

I can see that line 4 is not required but this always returns 0. Let's say TotalA is 100 & TotalSuccess is 10 I would expect the output to = 10% or at least 10.

Am I missing something simple?

Cheers

Re: Adding 2 searches together sounds easy

niketn — Thu, 01 Feb 2018 17:01:44 GMT

@rob3770, can you post the code with code button (101010) so that special characters do not escape?
Also are the index and source same for the sub-searches used in appendcols? and What is the current output of your query?

Re: Adding 2 searches together sounds easy

rob3770 — Thu, 01 Feb 2018 17:09:53 GMT

index=ABC source="E:\\Logfiles\\OneClick\\Operations.log"   ServiceName=DEF  |  
stats distinct_count(CorrelationId) as TotalA | 
appendcols [search  "TokenStatus=*Success*" ServiceName=DEF | stats distinct_count(CorrelationId) as TotalSuccess ]| 
appendcols [search TokenSubStatus=*error* ServiceName=DEF | stats distinct_count(CorrelationId) as TotalFailure ]| 
eval Total=(TotalSuccess*100)/TotalA | fields Total

Hi i have added the query as requested
I have amended the index and sources for security but the sources are all the same
The output is always 0

I have tried eval Total=(TotalSuccess+100)/TotalA | fields Total and get 100

Many thanks

Re: Adding 2 searches together sounds easy

somesoni2 — Thu, 01 Feb 2018 17:24:31 GMT

Try this

index=ABC source="E:\\Logfiles\\OneClick\\Operations.log"   ServiceName=DEF  
| eval Success=if(match(TokenStatus,"Success"),CorrelationId,null())
| eval Failure=if(match(TokenStatus,"error"),CorrelationId,null())
|  stats dc(CorrelationId) as TotalA  dc(Success) as TotalSuccess dc(Failure) as TotalFailure
| eval Total=(TotalSuccess*100)/TotalA | fields Total

Re: Adding 2 searches together sounds easy

felipesewaybric — Thu, 01 Feb 2018 17:41:59 GMT

How about this way:

index=ABC ("TokenStatus=Success" OR TokenSubStatus=error) ServiceName=ABC 
| eval TotalSuccess = if(TokenStatus=Success,1,0)
| eval TotalFailure = if(TokenSubStatus=error,1,0)
| stats 
count as TotalA
sum(TotalSuccess) as TotalSuccess
sum(TotalFailure) as TotalFailure
| eval Total=(TotalSuccess*100)/TotalA | table Total

Re: Adding 2 searches together sounds easy

rob3770 — Thu, 01 Feb 2018 17:42:00 GMT

Hi, both your queries are returning 0

Cheers

Re: Adding 2 searches together sounds easy

felipesewaybric — Thu, 01 Feb 2018 17:50:59 GMT

try this one, if istill return zero, try the first line only, then 1,2 and 3 together, then 1 to 7

index=ABC ("TokenStatus=Success" OR TokenSubStatus=error) ServiceName=ABC 
 | eval TotalSuccess = if(TokenStatus=Success,1,0)
 | eval TotalFailure = if(TokenSubStatus=error,1,0)
 | stats 
 count as TotalA
 sum(TotalSuccess) as TotalSuccess
 sum(TotalFailure) as TotalFailure
 | eval Total=(TotalSuccess*100)/TotalA | table Total

Re: Adding 2 searches together sounds easy

rob3770 — Thu, 01 Feb 2018 22:28:16 GMT

index=wpap source="E:\\Logfiles\\OneClick\\Operations.log" ("TokenStatus=*Success*") ServiceName=BILLDESK 
| eval TotalSuccess = if(TokenStatus=Success,1,0)

This provides the correct number of successes, the line concerning failures is a red herring and was left over by the original person.

stats distinct_count(CorrelationId) as TotalA |

This is the line which counts the number of unique ID's and should be used in the calculation against the Success number (ID*100/Success)