Hi @adent, Only one question, if you have events in more days, an event at previous day 23.59 is earlier than an event at second day 1.30, but calculatig min on the time value it's different. Do you want to calculate min only on time or the earliest timestamp?  If min on time, you should run something like this: <your_search> | eval Time=strftime(_time,"%H.%M") | stats min(Time) AS Time BY event | append [ search <your_search> | eval Time=strftime(_time,"%H.%M") | stats min(Time) AS Time | eval Time=strftime(Time,"%H.%M") if the earliest timestamp, you could try something like this: <your_search> | stats earliest(_time) AS Time BY event | append [ search <your_search> | stats earlieste() AS _time | eval _time=strftime(_time,"%Y-%m-%d %H.%M") I could be more detailed if you share a sample of your logs and (if you already have) a search that you're using? Ciao. Giuseppe

Hi one old answer which describe how joins can/should do with splunk https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-join-and-the/m-p/391288/thread-id/113948 r. Ismo

I've had the exact same use case and found a work around. Added this just in case anyone else stumbles across it. Update the default option to noop, so it reverts to this when the checkbox is deselected <input type="checkbox" token="dedupresults"> <choice value="dedup src,dest">Dedup</choice> <default>noop</default> </input> And insert the token into your search.  .... | $dedupresults$ | ..... This will result in the search being either ... | dedup src,dest | .....  or ... | noop | .....

Using mvrange with time!  I think you also gave me this a long time ago for a different question, but with a unit instead of directly with _time. (mvexpand with info_max_time - info_min_time is too much.) Combining that lesson (thanks again!) and this formula, and working out some Splunk kinks, I can make it work with simple count. To start, I also realize that addinfo in makeresults will not work the same way as in a search command.  So, I modified my simulation strategy a little.  This will be my new baseline:   index = _internal | where _time < relative_time(now(), "-2h@h") ``` simulate zero-count buckets ``` | timechart span=1h count   The complete workaround will be index = _internal | where _time < relative_time(now(), "-2h@h") ``` simulate zero-count buckets ``` | bucket _time span=1h@h | chart count over _time | append [| makeresults | addinfo | eval hours = mvrange(0, round((info_max_time - info_min_time) / 3600)) | eval time = mvmap(hours, info_min_time + hours * 3600) | table time | mvexpand time | rename time as _time | bucket _time span=1h@h | eval count=0] | stats sum(count) as count by _time   Then, I should have noted in OP that my chart has a groupby clause.  So, I move my baseline to index = _internal sourcetype IN (splunkd, splunkd_access, splunkd_ui_access) | where _time < relative_time(now(), "-2h@h") ``` simulate zero-count buckets ``` | timechart span=1h count by sourcetype The workaround with groupby therefore is index = _internal sourcetype IN (splunkd, splunkd_access, splunkd_ui_access) ``` simulate zero-count buckets ``` | where _time < relative_time(now(), "-2h@h") | bucket _time span=1h@h | chart count over _time by sourcetype | append [| makeresults | addinfo | eval hours = mvrange(0, round((info_max_time - info_min_time) / 3600)) | eval time = mvmap(hours, info_min_time + hours * 3600) | table time | mvexpand time | rename time as _time | bucket _time span=1h@h | foreach splunkd, splunkd_access, splunkd_ui_access [eval <<FIELD>> = 0]] | chart sum(*) as * by _time This is super messy; it can be daunting if there are many values in groupby, or if values are unpredictable.  As you said, I should try to stick to timechart when dealing with time series.

It is interesting that you pose the question in regard to SPL instead of a data structure consideration.  But still, while array is a viable structure for many applications, SPL is not the only language that has to go to extra length to handle.  If you have a choice, and if you don't care much about front-end compute, hash is easier on SPL. (And again, easier in some use cases with other languages.) I do want to suggest, though, you drop the nested listX.id node because that is redundant. (Lastly, I also recommend that you illustrate with compliant JSON.  This makes volunteers work easier.) {"host": "test", "list1" :{                   "ip": "192.168.0.1",                    "device": "laptop",                    "value": 123              }, "list2" : {                    "ip": "192.168.0.2",                    "device": "phone",                    "value": 1223                    }, "list3":   {                    "ip": "192.168.0.3",                    "device": "desktop",                    "value": 99                    } }

Something like this should work. I called the lookup db_names.csv ... change that to whatever your actual lookup is named. Everything above the comment just emulates the data you gave. | makeresults count=1 | eval _raw="DatabaseName,Instance,CPUUtilization A,A1,10 A,A2,20 C,C1,40 C,C2,50 D,D,60" | multikv forceheader=1 | fields - _time, _raw, linecount ```^^^^ This emulates the data you gave ^^^^``` | eval inst_cpu=Instance+"#"+CPUUtilization | fields - Instance CPUUtilization | inputlookup db_names.csv append=true ```<-- change the lookup name here``` | stats list(inst_cpu) as inst_cpu by DatabaseName | mvexpand inst_cpu | eval Instance=mvindex(split(inst_cpu,"#"), 0) | eval CPUUtilization=mvindex(split(inst_cpu,"#"), 1) | fillnull value="NULL" Instance CPUUtilization | fields - inst_cpu    

You could do something like this. Imagine a lookup that looks like this. ip_lookup.csv ip 10.10.53.22 127.0.0.1 192.168.0.54     index=myindex ([| inputlookup ip_lookup.csv | stats values(eval("ip=\""+src_ip+"\"")) as search | eval search=mvjoin(search, " OR ")])   This produces ...   index=myindex (src_ip="10.10.53.22" OR src_ip="127.0.0.1" OR src_ip="192.168.0.54")   ... or ...   index=myindex ([| inputlookup ip_lookup.csv | stats values(eval("src_ip!=\""+ip+"\"")) as search | eval search=mvjoin(search, " AND ")])   This produces ...   index=myindex (src_ip!="10.10.53.22" AND src_ip!="127.0.0.1" AND src_ip!="192.168.0.54")     You could even just put the sub-search into a macro that references the lookup to make using it easier for reuse. An example would be like this. Macro name: my_ip_macro Macro definition:   [| inputlookup ip_lookup.csv | stats values(eval("src_ip=\""+ip+"\"")) as search | eval search=mvjoin(search, " OR ")]     Search using the macro:   index=myindex `my_ip_macro`    

What do you mean by pulling the _raw? Do you mean "pulling" as in removing _raw from the fields list? Are you using the collect command to add the events into another index? If you do and don't explicitly set a sourcetype then you will not incur a licensing hit for the data copied to the other index.

Here are a couple posts that cover this concept: Solved: Search for items not matching values from a lookup - Splunk Community Solved: Compare search results with a lookup table and ide... - Splunk Community