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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...