Need help in append

[srinivasgowda]

Hello all,

I am facing an issue in appending an query. Here my objective is to update the kv store with the list of servers, alert_flag(if the alert has been raised) and count(number of times the server has created an event). Below is the query that I have used.

index= index
| lookup source_host_kvstore_001 source_host OUTPUT source_host as temp_source_host count alert_flag| dedup source_host
| eval count=if(isnull(count),0,count)| eval count = count+1
| eval alert_flag = if(isnull(alert_flag),0,if((alert_flag=1),1,0))
| eval _time=now()
| table _time source_host alert_flag count | sort -_time
| outputlookup source_host_kvstore_001 append=true

When the above is ran everytime the same host is updated and also added in the new row, however, I need a single update of the count and alert_flag for a host. The data is pushed to the kv store as below by a new increase in the count.

_time             alert_flag            count           source_host
2021-03-05 13:01:50      0         1          Server 1
2021-03-05 13:01:50      0         1          Server 2
2021-03-05 13:01:50      0         1          Server 3
2021-03-05 13:01:53      0         2          Server 1
2021-03-05 13:01:53      0         2          Server 2
2021-03-05 13:01:53      0         2          Server 3

However, I am looking for the data to be updated in the KV store like below.

_time             alert_flag            count           source_host
2021-03-05 13:01:53      0         2          Server 1
2021-03-05 13:01:53      0         2          Server 2
2021-03-05 13:01:53      0         2          Server 3

Please guide me through this.

Regards

[srinivasgowda]

Hello @manjunathmeti ,

Thanks for the quick response. This is still giving the same result by adding new rows for the same source_host in the kvstore. I am looking to have a singe row for each source_host and just the count to increase everytime there is an event from the source_host.

Regards

[ITWhisperer]

You need to use append=false

[srinivasgowda]

This will update the count for the source_host, however, if a new source_host come in then the existing data in the kvstore would be deleted.

[ITWhisperer]

Can you append / union the current contents of the store so your search includes everything you want before you output it?

[srinivasgowda]

Yes, append=false works as long as the same set of source_host is repeated in every run, but if in a run there is events from just 1 source_host then the remaining in the kvstore would be deleted updating just the one that was currently present.

[ITWhisperer]

Exactly, so use inputlookup as part of the search to append or union the current contents of the keystore

[srinivasgowda]

inputlookup does not work after using outputlookup.

[ITWhisperer]

Use inputlookup (to get current contents) before outputlookup (to write full set)

[manjunathmeti]

hi @srinivasgowda,

Use stats with latest function to get latest values by source_host.

index= index
| lookup source_host_kvstore_001 source_host OUTPUT source_host as temp_source_host count alert_flag
| eval count=if(isnull(count),1,count+1)
| eval alert_flag = if(isnull(alert_flag),0,if((alert_flag=1),1,0))
| eval _time=now()
| fields _time source_host alert_flag count 
| stats latest(_time) as _time latest(*) as * by source_host
| outputlookup source_host_kvstore_001 append=true