Triggering third-party authentication Verification

[chimuru84]

Hello! I'm trying to implement a mechanism to flag users who have not had a third-party authentication verification in the last 365 days.

I tried this search, but is not give desired result.

index=......
| stats count by id
| search id=*
| eval Duration=relative_time(now(), "-365d@d")
| sort id
| table id Duration | dedup id

I'm grateful for any ideas. Thanks.

[gcusello]

Hi @chimuru84 ,

you should have a list of your users to be inserted in a lookup (called e.g. users.csv) with one column "id".

then you could run something like the following:

index=...... earliest=-365d
| stats count by id
| append [ | inputlookup users.csv | eval count=0 | fields id count ]
| stats sum(count) As total by id
| where total=0

Ciao.

Giuseppe

[chimuru84]

It didn't help... I mean that a user has not made a check for 365 (or more) days until now.

[gcusello]

Hi @chimuru84 ,

with this search you take all the checks event in your logs and you compare them with the users list so you can define is there's some user that didn't do any check on the third party.

The main job is to extract the check events from your logs, and I cannot help you on this because I don't know your logs, then you can use my search to compare the results with the users list.

Ciao.

Giuseppe

[chimuru84]

Hi @gcusello! I think I didn't ask the question correctly. I want to make a query that returns the users who had a third-party authentication (at the moment), and the last time they passed the authentication was 365 days ago.

[gcusello]

Hi @chimuru84 ,

sorry, I  ,isunderstood yur requirement!

let me understand: you want to know the users connected to a third party authentication in the last hour that didn't do another connection in the last year but they did before, is it correct?

at first: how long do you want to run your check: two years?

Then, when you say "authentication at the moment", are you meaning in the last hour or what else?

With the above hypotesis

So, please try this:

index=...... earliest=-2y latest=-h [ search index=...... earliest=-h latest=now | dedup id | fields id ]
| eval period=if(_time>now()-31536000, "last Year","Previous Year")
| stats 
     dc(Period) AS Period_count 
     values(Period) AS Period 
     BY id
| where Period_count=1 AND Period!="Previous Year"
| table id

In ths way, you have yje users connected in the last hour  that did the last connection (except the last hour) more than one year.

If you need a different condition, you can use my approach.

Ciao.

Giuseppe

[chimuru84]

Hello again @gcusello ! Sorry again. I want to return id, nr_of_days (difference between last_date and_first_date), login of last_date (could be today, yesterday, 1 month ago etc.) and login of first_date (where first_date is 365 days or more). 

[gcusello]

Hi @chimuru84 ,

in this case you have to add some fields to the stats command, but the approach is always the same:

index=...... earliest=-2y latest=-h [ search index=...... earliest=-h latest=now | dedup id | fields id ]
| eval period=if(_time>now()-31536000, "last Year","Previous Year")
| stats 
     dc(Period) AS Period_count 
     values(Period) AS Period 
     earliest(_time) AS first_date
     latest(_time) AS last_date
     BY id
| where Period_count=1 AND Period!="Previous Year"
| eval 
     nr_of_days=last_date-first_date,
     first_date=strftime(first_date,"%Y-%m-%d %H:%M:%S"),
     last_date=strftime(last_date,"%Y-%m-%d %H:%M:%S")
| table id nr_of_days first_date last_date

Ciao.

Giuseppe