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!

.conf24 | Registration Open!

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

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...