Unable to use stats on summary index

[606866581]

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

[somesoni2 · Accepted solution]

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]

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

[JDukeSplunk]

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)

[JDukeSplunk]

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 

[606866581]

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.