Yep, I've been conceded to store data as Kvstore lookups (it's a large table.) It is a struggle because I have a personal dislike for lookups due to the search logic being abstracted and its stanza is a pain in butt to locate in a savedsearches.conf file. Why use lookups when tstats gives the result in 3 seconds? Could save tstats as a macro too. ... View more

I think the unfortunate reality with this specific JSON structure for tags is equivalent to a multivalue when converted with spath. When you perform latest() like you suggested, and even latest() on a multivalue field, it returns only a single value from a multivalue list. The mvcount will always return exactly 1 or 0 if empty. Values() has to be used instead of latest() to capture the full list, BUT there is no guarantee all values are factually latest _time. If a tag value was removed 2 days ago, I don't want that tag in my report. This is why I try to parse _raw to capture whole JSON objects with all tags nested within. This way, the entire JSON object survives latest() function with its nested tag list intact. Note the AWS's IMDSv2 metadata has this exact same JSON structure so this observed problem persists in AWS space as well. As instance tagging is industry standard, isn't some random edge case. What Splunk really is missing is something like latest_values() function which could allow for values() to function and its values _time matches the latest(_time) of a unique identifier field. ... View more

Test post. Wasn't able to post? Edit: Okay, it works. Yes that is an caveat to bring up. Fortunately, you can use a foreach with an iterator to make each value in the multivalue unique. I'm thinking it is something like the following. I'm sure its not impossible to add a custom unique identifier to each value in mv field nonetheless.   | eval iterator=0 | foreach <multivalue_field> [eval iterator=iterator+1, <<ITEM>>=iterator."-".<<ITEM>>] ``` Warning: Did not test this yet ```   Then you can perform the reverse stats join, and use split() and mvindex() to parse out your actual values without needing regex! You are correct, I was indeed working with a multivalue of unique identifiers which is why it worked for me. ... View more

Wow. For all my queries, I had been using the following fields command under the assumption it did drop _raw.   | fields _time, xxx, yyy, zzz, ....     Then one day I started a large mvexpand and ran into memory limit. My thought upon seeing this was 'Huh? Well, worth a try I guess.'   | fields _time, xxx, yyy, zzz, .... | fields - _raw   Boom, mvexpand completes successfully. The heck? It actually cut the search time in half too. ... View more

This the real answer. Still valid as of 2024 for the VMware TA add-on. GUI did not work for installation. Had to copy the tgz into the directory and extract it. Restarted Splunk and it works. ... View more

Update in case anyone tried testing to see if "append" option exists, the "append" option does actually save but appears to not work.   ... View more

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