Splunk Search
Highlighted

Unable to use stats on summary index

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 LastIP and a CustID.

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

0 Karma
Highlighted

Re: Unable to use stats on summary index

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
Highlighted

Re: Unable to use stats on summary index

Path Finder

The count(CustID) 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(LastIP), but it suffers from the same problem.

0 Karma
Highlighted

Re: Unable to use stats on summary index

Builder

If you just run

index=lastipsummary

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
Highlighted

Re: Unable to use stats on summary index

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

Highlighted

Re: Unable to use stats on summary index

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