Thank you for your answer. At the end, I would like to see if 2 persons are actually using the same pass, which I can see if they are checking in without the other checking out first.
The code you have written gave me the lines in italic and bold. The thing is that there is no disctinction between people using the same pass or poeple using a pass, checking out and then checking in again.
_time, id, checkedin, checkedout
2019-03-18 15:00:00.0, someone1, 1, 0
2019-03-18 16:15:00.0, someone1, 0, 1
2019-03-18 17:00:00.0, someone1, 1, 1
2019-03-18 15:30:00.0, someone2, 1, 0
2019-03-18 16:30:00.0, someone2, 0, 1
2019-03-18 15:00:00.0, someone3, 1, 0
2019-03-18 15:30:00.0, someone3, 1, 0
2019-03-18 16:15:00.0, someone3, 0, 2
2019-03-18 15:30:00.0, someone4, 1, 0
2019-03-18 16:15:00.0, someone4, 0, 1
... View more
So I have data like these:
**_time, id, event**
2019-03-18 15:00:00.0, someone1, checkedin
2019-03-18 16:00:00.0, someone1, X
2019-03-18 16:15:00.0, someone1, checkedout
2019-03-18 17:00:00.0, someone1, checkedin
2019-03-18 17:15:00.0, someone1, checkedout
2019-03-18 15:30:00.0, someone2, checkedin
2019-03-18 16:30:00.0, someone2, checkedout
***2019-03-18 15:00:00.0, someone3, checkedin
2019-03-18 15:30:00.0, someone3, checkedin
2019-03-18 16:15:00.0, someone3, checkedout
2019-03-18 16:30:00.0, someone3, checkedout***
2019-03-18 15:30:00.0, someone4, checkedin
2019-03-18 15:45:00.0, someone4, X
2019-03-18 16:15:00.0, someone4, checkedout
I do not care about the lines with the event is different than "checkedin" or "checkedout" ("X" here).
I want to be able to detect when a person checked-in twice or more with the same id before checking out (look at bold and italic lines). And I want to return all lines or, at least id, where this is the case. I want to seperate my results per id.
As I wanted to group per id but I also make a disctinction per event, my first codes were:
(event="checkedin" OR event="checkedout") | timechart span=15m count(id) by event
(event="checkedin" OR event="checkedout") | timechart span=15m count(event) by id
But they clearly do not give me what I want.
I also thought about multiple searches but as you cannot put streaming functions, it is not working.
Do you have an idea how to do it please?
... View more
Thank you very much, it is working!
Sorry for the delay, it happened that I had to work somewhere else for some time and on another topic. I am back again and I will stay there now.
To rephrase what I wanted for the next poeple reading this: "The lookup is performed for all events. If there is no match between the lookup table and the old output field, the value of the old outputfield is kept. And if there s a match, it is the result of the lookup that is kept. And those results are in the same new output field.".
result of the initial search:
the result I wanted:
Here is the code the code that worked (Thanks to @rrich7177!):
SEARCH | lookup lookuptable_name.csv id AS id OUTPUT name AS MyTempOutput
| eval name_ = if(MyTempOutput != "", MyTempOutput, id) | fields name_,item
... View more
I have a column with names, I will call it "Costumers_Names". The "names" are actually unique identifiers (unique IDs).
I also have a short list with a correspondance between IDs and actual names of the costumers. But I do not have all the names correspondance.
I would like, at the end, to have, in the "Costumers_Names" column, the actual names of the costumers (for those where I know the correspondance between the IDs and the names), and for those where I do not know the correspondace, I would like to keep the unique IDs I already have.
I thought about trying to create a lookup table with only the correspondances I know about. The problem is: When I try replacing, I have a blank space for those I do not know the name.
As there is always new Costumers and Costumers that disapear, I think that it would take me too many ressources to run a script that would run every day to update a lookup table that would look like
Moreover, I do not want to delete a Costumer ID just because he/she was not in active one day.
I also do not want to put directly the correspondance in the search (with "eval" for example), because I have multiple searches and alerts where I use that correspondance. So I would like to have something like just 1 file where I can put the correspondances I know and use it in many searches.
Therefore, do you think it is possible to replace the names of a field, thanks to a lookup table, but only when they exists in the lookup table, and keep the original name if not?
Or do you have another idea without a lookup table?
... View more