topic Re: How do I create15 min time buckets in QlikView

How do I create15 min time buckets

isciberras — Fri, 15 Dec 2017 14:59:28 GMT

Hi Everyone,

I am currently in the process of creating a password analytics dashboard.

One of the requirements is to create a list of people who have entered their password incorrectly more than once in the span of 15 min.

The data I have is:

Password Failure Counter	Date	Time	Employee
1	01/02/2017	15:50:00	1
3		09:15:30	2

Can anyone help me please?

Thanks,

Isaac

Re: How do I create15 min time buckets

m_woolf — Fri, 15 Dec 2017 17:14:48 GMT

You could create a new field by rounding the Time to the lowest 15 minutes:

=floor(Time*96)/96 as TimeBucket

Re: How do I create15 min time buckets

rubenmarin — Fri, 15 Dec 2017 18:18:22 GMT

Another similar option:

=Time(Floor(Time, 1/96))

Re: How do I create15 min time buckets

isciberras — Mon, 18 Dec 2017 12:03:03 GMT

Thank you all for your help much appreciated.

The time buckets worked however this didn't seem to solve the issue I am facing after all.

The point of this exercise is to see if anyone attempted a failed and successful login within a 15 min time span. Keeping In mind I have loaded in multiple days would you happen to know if this is possible?

Can anyone please help me out?

Re: How do I create15 min time buckets

rubenmarin — Mon, 18 Dec 2017 15:37:08 GMT

Hi Isaac, initial question ws about counting incorrect logins, wich can be done with a table by employee and the bucket counting incorrect logins >1, but it won't count failed logins at xx:14 and xx:16 in the same bucket.

Loding sorted by employee and login you can check if the difference with prevuious failed login was in less than 15 minutes:

LOAD

If(Peek(Employee)=Employee, If(DateTimeField-Peek(DateTimeFieldLoaded)<1/96, 1, 0) isLessThan15Minutes

// Add conditions to check if both are failed logins?

...

From/Resident ...

Order by Employee, DateTimeField;

To know a successful login after a failed login more conditions needs to be added and a field to know when a login was failed or successful.

Re: How do I create15 min time buckets

isciberras — Mon, 18 Dec 2017 16:19:52 GMT

Hi Ruben,

Thank you for your reply much appreciated. I am starting to follow your syntax but I'm still not managing to get it to work. I don't get any errors in the script but there is no data in the field I am creating (Basically I am loading in a blank field). Can you kindly guide me a little bit more please?

So far I wrote:

____________________

Load

If(Peek(USR)=USR, If(time-Peek (time) <1/96,1,0)

Resident table1

____________________

However this doesn't check If there was any time of login attempt of password failure counter check,

Re: How do I create15 min time buckets

rubenmarin — Mon, 18 Dec 2017 16:25:44 GMT

I suppose USR is the employee... it also needs to be loaded in the table as 'USR' for the Peek().

I used Datetime to control spans between days, with only time it will check spans in the same day.

The '...' should be completed with your actual table data, adding this calculated field.

You can try loading the expression in different fields to check what fails:

- Peek(USR) as peekUSR,

USR,

time

Peek(time)

or upload a sample to make some tests.

Re: How do I create15 min time buckets

isciberras — Mon, 18 Dec 2017 16:27:32 GMT

Thank you for your direction Ruben fingers crossed I will get this to work. And yes sorry I fogot to mention USR is the employee

Thanks,

Isaac

Re: How do I create15 min time buckets

rubenmarin — Mon, 18 Dec 2017 20:00:02 GMT

Good luck, if doesn't work try to create a sample that demonstrates the problem and I'll try to give a solution.