Splunk Search

Why is stats "first" function showing multiple results for a field?

loeweps
Explorer

I have the following data. Each one has a different date entry.

DATE      ACCOUNT_NUMBER  SOLUTION  NAME  ADDRESS
1-1-2015         1        Replaced  NAME1 ADDRESS1
1-1-2015         2        Repaired  NAME2 ADDRESS2
1-1-2015         3        Repaired  NAME3 ADDRESS3
1-2-2015         1        Repaired  NAME1 ADDRESS1
1-2-2015         2        Replaced  NAME2 ADDRESS2

I have a search with a subsearch:

index=data  [ search index=data | stats count by ACCOUNT_NUMBER | where count>1 | fields ACCOUNT_NUMBER ] | stats latest(ACCOUNT_NUMBER) count    by SOLUTION 

Problem is when I add count by ACCOUNT_NUMBER,SOLUTION, I still have account numbers showing up with a result of more than 1.

I have tried first(_time), first(ACCOUNT_NUMBER) along with other searches. What I want is to see the first solution used for each account. The above result would be :

ACCOUNT_NUMBER  SOLUTION
1               Replaced
2               Repaired
3               Repaired

And a count of the above which is my ultimate goal would be:

SOLUTION  count
Replaced    1
Repaired    2 

Appreciate any help that can be provided. Thank you for taking the time to respond.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try this (not sure if the subsearch was required so just excluded them.

Query: 

index=data | stats latest(SOLUTION) as SOLUTION by ACCOUNT_NUMBER

Output:
     ACCOUNT_NUMBER SOLUTION
    1 Replaced
    2 Repaired
    3 Repaired

and Query: 

index=data | stats latest(SOLUTION) as SOLUTION by ACCOUNT_NUMBER | stats count by SOLUTION

Output: 
     SOLUTION count
    Replaced 1
    Repaired 2

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try this (not sure if the subsearch was required so just excluded them.

Query: 

index=data | stats latest(SOLUTION) as SOLUTION by ACCOUNT_NUMBER

Output:
     ACCOUNT_NUMBER SOLUTION
    1 Replaced
    2 Repaired
    3 Repaired

and Query: 

index=data | stats latest(SOLUTION) as SOLUTION by ACCOUNT_NUMBER | stats count by SOLUTION

Output: 
     SOLUTION count
    Replaced 1
    Repaired 2

loeweps
Explorer

That worked. Subsearch was only there to filter out the accounts with less than one entry.

Thank you again for taking the time to respond. I didn't think to try solution as I was focused on the account number field.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...