This is great, and long story short for your two qualifiers: Yes to both two (#1 and #2). I was indeed using a combined search as well. Now for tstats, I really like your idea. The concern I had is, let's say I do have a sourcetype_1 with over 1,000,000 unique sourcetype_1_primary keys. This sourcetype is also incremental, so any "net-new" changes for any of the 1,000,000 primary keys are dumped into Splunk once every 24 hours and not all of the 1,000,000 keys are not updated every day. My rule of thumb is to look back a maximum of 30 days to catch all the changes and use stats latest() to create the latest data for each of the 1,000,000 primary keys. So with your tstats example, it seems to only work for sourcetypes with full data dumps each day if the specific length between latest and earliest is known, instead of incremental sourcetypes. Else, I could have set earliest=-24h and be done with it. It's actually kind of ironic knowing how Splunk searches work with timeframes. Assuming you're searching with 'earliest' time modifier and latest is now(), Splunk does search backwards from now() to the earliest. In other words, searches backwards from latest to earliest. You can see the Splunk search working backwards in real time by observing the 'Timeline' under the ad-hoc search pane. With my understanding that Splunk does search backwards, I just wish there's a way which when Splunk is doing the index searches, there's a way to tell Splunk to just keep only the latest event of each unique value of a field. For example: When doing Index searches, tell Splunk to keep only the first occurring event of each unique value in the field sourcetype_1_primary. Splunk is to ignore any subsequent duplicate values as Splunk continues to search backwards. Edit: I'm not describing streamstats command aren't I? Edit2: I converted my stats latest() to streamstats latest() and did not see improvements. Additionally, streamstats appear to break the ability to do stats join when switching it from stats values() to streamstats values(). Appears streamstats work correctly only for latest() but not when joining data.   ... View more

Thank you! The first appendpipe achieved the desired objective! The size constraint should not be a problem because I had all the unixtime snapped to the month with @mon so there's only 300 rows in this table. The way to explain this odd situation is that each day, we get the data dump of the population but their field values may change by the day. The issue is that Splunk has a 90 day data retention policy for our events. So basing events purely on _time only goes back 90 days. BUT, in our events, there are additional unixtime fields (two to be exact) that go back much further than 90 days and we needed to use these to provide a historical month by month view (hence snapping unixtime with @mon). Total_A was the total sum of the population over time based on Unixtime_A, and Total_B is a conditional sum of the population where only a field met a condition, and Unixtime_B contained the time this condition was first met. That's why I wanted Total_A and Total_B to be seperate, but Unixtime_A and Unixtime_B could be appended together. To put some context to it, Total_A is total vulnerabilities population regardless of whether it was fixed or active based on Unixtime_A being when it was first detected. Total_B is total fixed vulnerabilities population based on Unixtime_B being when it was fixed. ... View more

Thanks for the reply! My team ended up having an OnDemandService request to look into this. Will report back. To answer your question by question. The irony is that it was originally CSV that we have since converted to kvStore for performance. The CSV file had became big, like 250 MB if I recall. This is the Qualys Knowledge Base we are talking about that Qualys provide out of box so it is not something we can trim down to size as CSV. The row count after the first stats and before lookup is 1,926,000. The fact stats calculates this in 20 seconds is perfectly fine. The problem becomes when lookup is used, it puts on an extra 140 seconds or so according to the Job Inspector. The dc(HOST_ID) ultimately ends with 7,800 rows. Now for your suggested approach - good catch for the stats. That works great for this particular query that only cares about PATCHABLE. In fact, the last stats can be changed from dc() to just count. Much faster at 50 seconds now! At some point however, we will need to provide full vulnerability data pulled from Qualys Knowledge Base through Splunk as the means of reporting for the engineering teams. We will run into this problem of the lookup hanging up for at least 140 seconds again. Regarding poor performance, when you say 'replicate' - is this what you mean for collections.conf? Because the kvstore is already replicated. [qualys_kb_kvstore] accelerated_fields.QID_accel = {"QID": 1} replicate = true  I think ultimately, we were under the impression the kvStore replication to the indexers makes it so there's a local copy handy for them, making a lookup really fast in matter of seconds. Maybe we had the wrong expectation? ... View more

5 years later and I came upon this. This should be the actual, concise answer for OP's question. ... View more

FYI as of 2024: This command would reach limit specified in limits.conf. As default, it would return 10,000 events, even if there's more than that. Instead, use: | sort 0 -_time This would return the full result, although can impact performance. ... View more

Thank you. For Splunk, would you say it is currently impossible to be able to show/hide the individual fields?* No alternative workarounds? (*To clarify, mimicking the behavior of the showDataLabels setting for the individual fields.)    ... View more