Hi,
I wonder whether someone may be able to help me please.
I have created in a separate search with a lookup table containing src_user, StartTime, and action (which its value is connected): It adds all the connected users to lookup table with the time:
source=........ VPNaction=connected |dedup src_user _time |eval
StartTime=strftime(_time,"%m/%d/%Y %H:%M:%S") |eval action=VPNaction|table src_user StartTime action
|outputlookup ConnectedVpn.csv createinapp=true
Now I want to look for the ended connection and compare the end time and start time:
source=..... VPNaction=ended |dedup src_user _time |eval
EndTime=strftime(_time,"%m/%d/%Y %H:%M:%S") |eval action=VPNaction|table src_user EndTime action | lookup ConnectedVpn.csv src_user OUTPUT StartTime |eval diff=EndTime-StartTime|table src_user StartTime action EndTime diff
How can I remove the row of the user whose connection is ended from ConnectedVpn.csv, otherwise it will cause problem for its next start.
Thank you
This should do the trick:
|inputlookup blah | where field!=itemtoremove | outputlookup create_empty=true blah
|inputlookup blah | search field!=itemtoremove |outputlookup blah
This will look at current csv remove the rows you don't want then overwrite the csv with only the data you want to keep
Run it without the ouptlookup first so you can see what you are going to replace with for safety
Thanks , Actually I want to find the users whose vpn connection is more that 24h, so in the second command as you see on the top I try to calculate the difference but I will also need to delete the records for the users who ended their connection, the only field that is the same with the lookup table is src_user
Outputlookup is also shc aware so it would replicate if you are using shc
I want to remove based on my search so when you say field!=removeitem how I can define it?
|inputlookup ConnectedVpn.csv |WHERE EndTime="" OR isnull(EndTime) |outputlookup ConnectedVpn.csv
Based on what I think your data is
CSV lookup files cannot be edited - they must be replaced completely or appended to.
KV Store lookups, however, can modified individual records.
Thanks, so in this case how can I run my scenario?
That's a good question. Ideally, you'd end your query with a REST
command that updates the KV store. Unfortunately, REST
is a generating command that must start a query so that idea won't work.
Perhaps a KV store expert will have another suggestion.