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.
|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
CSV lookup files cannot be edited - they must be replaced completely or appended to.
KV Store lookups, however, can modified individual records.
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.