Splunk Search

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

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

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

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

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