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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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