Splunk Search

How to create a search where two values of a field should not overlap with time?

acathignol
Explorer

Hello,

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

or

(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?

0 Karma

Vijeta
Influencer

@acathignol you can try something like this, it will give you login and logout counts in a 15 minute time span per id.

 <your query>| bin span=15min _time|stats count(eval(event="checkedin")) as checkedin, count(eval(event="checkedout")) as checkedout by id _time| where checkedin > checkedout
0 Karma

acathignol
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...