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
| 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)
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")
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
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
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
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
I’m not getting any field login attempt . I’m getting a zero value . That’s what the issue is .
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
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.
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
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
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