Splunk Search

Adding 2 searches together sounds easy

rob3770
Explorer
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

Tags (1)
0 Karma

felipesewaybric
Contributor

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
0 Karma

rob3770
Explorer

Hi, both your queries are returning 0

Cheers

0 Karma

felipesewaybric
Contributor

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
0 Karma

rob3770
Explorer
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)

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

niketn
Legend

@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?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

rob3770
Explorer
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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...