topic Re: How to get user once per minute? in Splunk Search

How to get user once per minute?

moiezuddin — Mon, 27 Apr 2015 13:43:35 GMT

I have a query

index=casm_prod sourcetype=smtrace "Center realm" | rex "(?i) Realm\\]\\[\\]\\[\\]\\[\\]\\[\\]\\[\\]\\[(?P[^\\]]+" |bucket _time span=1m |stats count(user) as eventcount by _time, user

From the above query i am unable to get the result

Requirement what i need is .............looking for a user once per mintue in dashboard

Kindly correct my Query

Re: How to get user once per minute?

MuS — Mon, 27 Apr 2015 14:07:05 GMT

Can you provide some sample events please?

Re: How to get user once per minute?

neelamssantosh — Mon, 27 Apr 2015 15:08:01 GMT

At the end of your query use ,
|stats values(user) as user count(user) as eventcount by _time

use user also after by clause i.e,
|stats values(user) as USER count(user) as eventcount by _time user|fields USER eventcount

But i don't think that can be fruitful.
Let me know if need more assistance.

Updated Query with span of 1min...
index=casm_prod sourcetype=smtrace "Center realm" | rex "(?i) Realm\]\[\]\[\]\[\]\[\]\[\]\[(?P[^\]]+" |bucket span=5m _time|stats values(user) as USER count(user) as eventcount by _time |fields USER eventcount

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 05:46:45 GMT

this is a production application
and dedup on user does not give us accurate information
we are not looking for the most recent login
but rather ALL logins that happened

this is why we are only looking for a user once per minute.

Re: How to get user once per minute?

moiezuddin — Mon, 28 Sep 2020 19:38:16 GMT

4/27/15
3:37:00.000 PM

[04/27/2015][12:37:57.821][992086960][s5036427/r60][Center realm][][][][][][206433741][][][][][][centerusushwswp222lprd][Send response attribute 147, data size is 0][]
index = casm_prod
4/27/15
3:37:00.000 PM

[04/27/2015][12:37:57.821][992086960][s5036427/r60][Center realm][][][][][][206433741][][][][][][centerusushwswp222lprd][Send response attribute 146, data size is 0][]
index = casm_prod
4/27/15
3:37:00.000 PM

[04/27/2015][12:37:57.821][992086960][s5036427/r60][Center realm][][][][][][206433741][][][][][][centerusushwswp222lprd][Send response attribute 224, data size is 16][sso_id=206433741]
index = casm_prod sso_id = 206433741
4/27/15
3:37:00.000 PM

[04/27/2015][12:37:57.821][992086960][s5036427/r60][Center realm][][][][][][206433741][][][][][][centerusushwswp222lprd][Send response attribute 224, data size is 16][smuser=206433741]
index = casm_prod
4/27/15
3:37:00.000 PM

[04/27/2015][12:37:57.821][992086960][s5036427/r60][Center realm][][][][][][206433741][][][][][][centerusushwswp222lprd][Send response attribute 224, data size is 22][georaclehrid=206433741]
index = casm_prod
4/27/15
3:37:00.000 PM

[04/27/2015][12:37:57.821][992086960][s5036427/r60][Center realm][][][][][][206433741][][][][][][centerusushwswp222lprd][Send response attribute 224, dat

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 06:38:35 GMT

Showing multiple login per minute

can we do it only one login per minute without using dedup

Re: How to get user once per minute?

vganjare — Tue, 28 Apr 2015 06:55:20 GMT

Will stats by date_minute, user help instead of using stats by _time,user?

Re: How to get user once per minute?

neelamssantosh — Tue, 28 Apr 2015 06:58:11 GMT

Good option but will not help us as we will miss the Time field.

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 06:58:13 GMT

Can you give me query clearly i am unable to understand

Re: How to get user once per minute?

fdi01 — Tue, 28 Apr 2015 07:18:38 GMT

try like :

...|bucket _time span=1m |stats count  as eventcount  by  _time,  user

...| timechart  per_minute(eval(count)) as eventcount by  _time  user

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 07:27:21 GMT

As requirement we need to use regex the time out instead of bucket span for below query

index=casm_prod sourcetype=smtrace "Center realm" | rex "(?i) Realm][][][][][][(?P[^]]+" |bucket span=5m _time|stats values(user) as USER count(user) as eventcount by _time |fields USER eventcount.

can you provide to me please,

Thanks.

Re: How to get user once per minute?

acharlieh — Tue, 28 Apr 2015 12:37:17 GMT

It sounds like you want the original _time value in the results? So something like:

index=casm_prod sourcetype=smtrace "Center realm" | eval minute=relative_time(_time,"@m") | stats first(_time) as _time, count as eventcount by minute, user

Re: How to get user once per minute?

acharlieh — Tue, 28 Apr 2015 12:42:57 GMT

Reading other comments are you meaning to use regex instead of rex ?

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 12:49:00 GMT

In the below query we used bucket span
but the requirement say need to use regex
index=casm_prod sourcetype=smtrace "Center realm" | rex "(?i) Realm][][][][][][(?P[^]]+" |bucket span=5m _time|stats values(user) as USER count(user) as eventcount by _time |fields USER eventcount

Re: How to get user once per minute?

acharlieh — Tue, 28 Apr 2015 12:54:43 GMT

Go into more detail. Why do you need to use a regular expression? What are you attempting to accomplish by it?

Re: How to get user once per minute?

acharlieh — Tue, 28 Apr 2015 12:56:17 GMT

Is this an assignment of some sort?

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 12:58:06 GMT

In the index for siteminder called cams_prod,
there are traced filed with the type smtrace.

Using these trace files find the logs for the application using 'Center realm’.

Then created a regular expression to mine the user.
You will notice that user are able to be found many times each minute.

We need to fiter this so it only shows once per minute.

Can you help in building it

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 14:12:39 GMT

Can any one regex the time out for below query insted of bucket span

index=casm_prod sourcetype=smtrace "Center realm" | rex "(?i) Realm][][][][][][(?P[^]]+" |bucket span=5m _time|stats values(user) as USER count(user) as eventcount by _time |fields USER eventcount

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 14:15:53 GMT

we need to use regex the time out instead of bucket span for below query

index=casm_prod sourcetype=smtrace "Center realm" | rex "(?i) Realm][][][][][][(?P[^]]+" |bucket span=5m _time|stats values(user) as USER count(user) as eventcount by _time |fields USER eventcount.

Please provide me the query

Re: How to get user once per minute?

moiezuddin — Tue, 28 Apr 2015 14:16:50 GMT

In the below query we used bucket span
but the requirement say need to use regex the time out

index=casm_prod sourcetype=smtrace "Center realm" | rex "(?i) Realm][][][][][][(?P[^]]+" |bucket span=5m _time|stats values(user) as USER count(user) as eventcount by _time |fields USER eventcount

Please any help on it