Splunk Search

Unable to use stats on summary index

606866581
Path Finder

I've created a summary index to keep track of my customer IDs and what their last IP address was, however I'm getting very strange behaviour while trying to use the fields.
This search runs successfully and produces a full set of results:

index=last_ip_summary | table Last_IP Cust_ID

This search runs successfully and produces a full set of results:

index=last_ip_summary | stats count by Last_IP Cust_ID

But this search only gives me the Last_IP field in the table, the other column is totally blank

index=last_ip_summary | stats values(Cust_ID) by Last_IP 

Surely this is impossible? Every event in the summary index has a Last_IP and a Cust_ID.

My summary index is populated with .... | sistats count by Cust_ID Last_IP
Any help would be greatly appreciated

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

It's because of the usage of sistats command for generating summary index data. If you're using the summary index specific aggregation commands to populate the summary index (e.g. sistats, sichart, sitimechart etc), then you should be creating a report, out of summary index data, with exact same syntax that you used while creating the summary index data. (see this ).

So, this will not return data for aggregated column

index=last_ip_summary | stats values(Cust_ID) by Last_IP 

but this will

index=last_ip_summary | stats count by Cust_ID Last_IP | stats values(Cust_ID) by Last_IP 

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

It's because of the usage of sistats command for generating summary index data. If you're using the summary index specific aggregation commands to populate the summary index (e.g. sistats, sichart, sitimechart etc), then you should be creating a report, out of summary index data, with exact same syntax that you used while creating the summary index data. (see this ).

So, this will not return data for aggregated column

index=last_ip_summary | stats values(Cust_ID) by Last_IP 

but this will

index=last_ip_summary | stats count by Cust_ID Last_IP | stats values(Cust_ID) by Last_IP 

606866581
Path Finder

Thank you Somesh, I changed my summary indexer to use stats instead of sistats so I now have the flexibility to use latest() and values() reliably

0 Karma

JDukeSplunk
Builder

If you just run

index=last_ip_summary

does the field Cust_ID contain a count in the fields list on the left? I'm just wondering count is failing because there is nothing to count? Also, on the left is the field prefixed by an 'a' or a '#' (indicating string vs number field)

0 Karma

JDukeSplunk
Builder

I know it should work, but maybe throwing a count function into stats will work..

 index=last_ip_summary | stats count(Cust_ID) AS Count by Cust_ID Last_IP 
0 Karma

606866581
Path Finder

The count(Cust_ID) column is 0. Everything after the by clause is populating in the stats table, so this is consistent with what I've observed above. Ultimately I want to get the latest(Last_IP), but it suffers from the same problem.

0 Karma
Get Updates on the Splunk Community!

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 ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...