Security

How to get a list of users who did not login to splunk indexer for more than 30 days?

Nilesh3110
Explorer

I need to find out the list of users who did not login to splunk for more than 30 days. I need to know when the user last login. I tried the below but did not get any result. can someone please help

index=_audit action="login attempt" | stats max(timestamp) by user
Labels (1)
0 Karma

vishaltv
Path Finder

| rest /services/authentication/users splunk_server=*
| fields title, realname, splunk_server, last_successful_login
| fillnull value=0
| eval days = now()-last_successful_login
| where days < 2592000
| table title, realname, splunk_server, last_successful_login
| convert ctime(last_successful_login)

0 Karma

woodcock
Esteemed Legend

Assuming that you are using CIM, you can do this:

| tstats max(_time) AS _time
FROM datamodel=Authentication
WHERE Authentication.dest=YourSplunkServerHere
AND nodename=Authentication.Successful_Authentication
earliest=0 latest=now
BY Authentication.user Authentication.dest
| where _time <= relative_time(now(), "-30d")
| eval age = tostring(now() - _time, "duration")
0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Check this. You can search indexer servers with splunk_server field.

| rest /services/authentication/users splunk_server=<indexer_server_name>
| fields title, realname, splunk_server, last_successful_login
| rename title as user_name
0 Karma

Nilesh3110
Explorer

Hi @manjunathmeti
This is what i had been trying to do the same thing, Its working fine for splunk users but not for LDAP users. We have alot of LDAP users who rarely use our box. I want to remove the LDAP users who do not use our Splunk Web.

For all LDAP users i see last_successful_login as blank. Can you please help

0 Karma

Nilesh3110
Explorer

This is what i had been trying to do the same thing, Its working fine for splunk users but not for LDAP users. We have alot of LDAP users who rarely use our box. I want to remove the LDAP users who do not use our Splunk Web.

For all LDAP users i see last_successful_login as blank. Can you please help

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Nilesh3110,
The search you need is a very slow search because you need to search in a very long period data.
So the best approach is to schedule (e.g. at 00.15) a daily search that stores logins in a summary index, something like this:

index=_audit action="login attempt" earliest=-d@d latest=@h
| dedup users
| eval day=strftime(_time,"%Y-%m-%d")
| table users day
| collect index=my_summary_index

and then run a search on this summary index on all time (or on a part of it):

index=my_summary_index
| stats max(day) AS day BY user

This should solve your need.

There's only one problem: if there are users never connected.
In this case you should create a lookup containing all the users to check and run a different search

index=my_summary_index
| stats max(day) AS day BY user
| append [ | inputlookup my_users_lookup | search NOT [ search index=my_summary_index | fields user ] | eval day="Never connected" | fields user day ]
| table user day
| sort user

Ciao.
Giuseppe

0 Karma

Nilesh3110
Explorer

I’m not getting any field login attempt . I’m getting a zero value . That’s what the issue is .

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Nilesh3110,
sorry: my error!

 index=_audit action="login attempt" earliest=-d@d latest=@h
 | dedup user
 | eval day=strftime(_time,"%Y-%m-%d")
 | table user day
 | collect index=my_summary_index

Ciao.
Giuseppe

0 Karma

Nilesh3110
Explorer

HI @gcusello : I am not getting any result when i am executing this SPL. Actually the action does not contain anything with
index=_audit action=login*
there is nothing in the action with login. I need to get the list of LDAP users last login date/time.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Nilesh3110,
what do you have running index=_audit action="login*" ?
You should have all the login to Splunk events.
If you haven't results, what result do you have with index=_audit | stats count BY action ?

Ciao.
Giuseppe

0 Karma

swaroopbr
Engager

Hi @gcusello, Below is the result what I get if I run index=_audit | stats count BY action

Please let me know what I can do next?

Results

action count
CREATE_PASSWORD 1
EDIT_PASSWORD 3
GET_PASSWORD 27983
REMOVE_PASSWORD 1
Remote token requested 69403
accelerate_search 24761
alert_fired 330
change_authentication 2366
created 7060
db_connect_read_connection 12
db_connect_read_dbinput 8
db_connect_read_dblookup 4
db_connect_read_dboutput 4
db_connect_read_identity 4
db_connect_read_settings 3379
db_connect_update_settings 7
deleted 6921
dispatch_rest_to_indexers 58
dmc_manage_forwarders 2
edit_deployment_client 361
edit_deployment_server 2554
edit_dist_peer 435
edit_forwarders 1083
edit_health 742
edit_indexer_cluster 3491
edit_local_apps 890
edit_modinput_admon 1072
edit_modinput_perfmon 1072
edit_modinput_winhostmon 1072
edit_modinput_winnetmon 1072
edit_modinput_winprintmon 1072
edit_monitor 884
edit_notable_events 7
edit_roles 10894
edit_roles_grantable 9815
edit_scripted 481
edit_search_head_clustering 109
edit_search_schedule_priority 2240
edit_search_schedule_window 2240
edit_search_server 361
edit_server 2258
edit_sourcetypes 402
edit_splunktcp 443
edit_tcp 646
edit_tcp_stream 722
edit_telemetry_settings 1385
edit_timeline 7
edit_token_http 1637
edit_udp 481
edit_upload_and_index 802
edit_user 3780
edit_win_eventlogs 1072
edit_win_regmon 1072
edit_win_wmiconf 1072
embed_report 264
failure 9
indexes_edit 1165
license_edit 1515
list_deployment_client 443
list_deployment_server 3618
list_forwarders 1206
list_health 15260
list_inputs 544
list_search_head_clustering 658
list_workload_pools 11309
list_workload_rules 424
modified 105620
output_file 530
quota 152285
read_itsi_notable_aggregation_policy 4
read_itsi_service 28
read_session_token 46677
rest_apps_view 742
rest_properties_get 2172
restart_splunkd 435
rtsearch 37
run_collect 87161
search 3216428
select_workload_pools 92418
splunkShuttingDown 4
splunkStarting 20
success 277

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Nilesh3110,
you should have something like this:

Audit:[timestamp=02-25-2020 10:16:14.657, user=admin, action=login attempt, info=succeeded reason=user-initiated useragent="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36" clientip=127.0.0.1 session=1b9eb86d9456806d7bfed51540a264d9][n/a]

Ciao.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...