KVstore update

[tlmayes]

I have the following "Frankenstein" query that creates a lookup table, and works quite well. Replaces several inadequacies of the Monitoring Console for tracking forwarders. This is only setup for the question (but you may have suggestions for this as well)

    index=_* host=sm008 OR host=sm007 OR host=sm004 "/services/broker/phonehome/connection" 
    | rename host AS manager | dedup clientip
    | stats max(_time) AS phonehome_time, values(manager) AS manager, BY clientip
    | join type=inner ind`enter code here`ex clientip 
    [ search index=_internal sourcetype=* source="*metrics.log" sourceHost=* date_zone=* group=tcpin_connections (connectionType=cooked OR connectionType=cookedSSL) fwdType=* guid=* version=* arch=* hostname=* sourceIp=* 
    | rename sourceIp AS clientip, hostname AS SrcHostname 
    | dedup clientip 
    | stats max(_time) AS connect_time, values(SrcHostname) AS SrcHostname, values(date_zone) AS TZ-Offset(reported), values(fwdType) AS fwdtype,  latest(version) AS Version, values(arch) AS Arch, values(os) AS OS,avg(tcp_eps) AS tcp_eps,  BY clientip]
    | eval "Avg EPS"=round(tcp_eps,2)
    | eval FwdType=upper(fwdType)
    | eval status_p = if( (phonehome_time < (relative_time(now(),"-600"))), "Missing", "Active")
    | eval status_c = if( (connect_time < (relative_time(now(),"-600"))), "Missing", "Active")
    | eval "LastPhoneHome"=strftime(phonehome_time,"%d-%b-%y - %H:%M:%S")
    | eval connect=strftime(connect_time,"%d-%b-%y - %H:%M:%S")
    | search manager=*
    | eval combined_status=status_p."/".status_c
    | rename clientip AS SrcIP, connect AS "LastConnected", combined_status AS "status", Version AS version, 
    | table SrcIP, SrcHostname, LastPhoneHome, "LastConnected", status, manager, fwdtype, version, OS

We now want to keep a history of and track of forwarders that have ever connected (we have more than 10,000 currently). I created a KVstore with the same fields as above, with one new one: host_record-date. This field will track date of the last time a forwarders IP appeared. Now I want to update in intervals the KVstore with information from the query above. Not all IP's in the KVstore will be update since some "randomly" disappear, so will only update those KVstore rows that have changed. Shouldn't the following query do this for me?

    | inputlookup admin_panel-KV-phonehome_indexing-status
    | join SrcIP type=outer [search index=_* host=sm008 OR host=sm007 OR host=sm004 "/services/broker/phonehome/connection" | rename host AS manager | dedup clientip
    | stats max(_time) AS phonehome_time, values(manager) AS manager, BY clientip
    | join type=inner index clientip 
    [ search index=_internal sourcetype=* source="*metrics.log" sourceHost=* date_zone=* group=tcpin_connections (connectionType=cooked OR connectionType=cookedSSL) fwdType=* guid=* version=* arch=* hostname=* sourceIp=* 
    | rename sourceIp AS clientip, hostname AS SrcHostname 
    | dedup clientip 
    | stats max(_time) AS connect_time, values(SrcHostname) AS SrcHostname, values(date_zone) AS TZ-Offset(reported), values(fwdType) AS fwdtype,  latest(version) AS Version, values(arch) AS Arch, values(os) AS OS,avg(tcp_eps) AS tcp_eps,  BY clientip]
    | eval "Avg EPS"=round(tcp_eps,2)
    | eval FwdType=upper(fwdType)
    | eval status_p = if( (phonehome_time < (relative_time(now(),"-600"))), "Missing", "Active")
    | eval status_c = if( (connect_time < (relative_time(now(),"-600"))), "Missing", "Active")
    | eval "LastPhoneHome"=strftime(phonehome_time,"%d-%b-%y - %H:%M:%S")
    | eval connect=strftime(connect_time,"%d-%b-%y - %H:%M:%S")
    | search manager=*
    | eval combined_status=status_p."/".status_c
    | rename clientip AS SrcIP, connect AS "LastConnected", combined_status AS "status", Version AS version, 
    | table SrcIP, SrcHostname, LastPhoneHome, "LastConnected", status, manager, fwdtype, version, OS]
    | table SrcIP, host_record-date, SrcHostname, LastPhoneHome, "LastConnected", status, manager, fwdtype, version, OS
    | outputlooklup admin_panel-KV-phonehome_indexing-status

[starcher]

Kvstore will update rows IF your _key field is consistent for the row AND you use ouputlookup append=true.

[tlmayes]

Odd... if I take everything between the outer [ ] and push to a lookup, and then perform the same process replacing the index statement with a inputlookup statement it all works as expected. A bug with Splunk, or a bug with the way I wrote the original query?

| inputlookup admin_panel-KV-phonehome_indexing-status
| join SrcIP type=outer [| inputlookup admin_panel-phonehome_status 
| rename FwdType AS fwdtype, "Last Connected to Fwdr-Idxr" AS LastConnected, "Last PhoneHome to Mngr" AS LastPhonehome, "Status(phonehome/connected)" AS status, Version AS version, "Forwarder Manager" AS manager
| fields SrcIP, manager, LastPhonehome, SrcHostname, OS, status, fwdtype, version, LastConnected]
| table *