Analysis on splunk users, for this i need to display
_time host user total_run_time searchQueryUsed Url
Please help me with splunk query
Try this. Not sure where you are getting total_run_time
, but this should give you everything else and hopefully a bit faster.
(index=_internal user=* sourcetype=splunkd_ui_access) OR (index=_audit action=search (search="\'rtsearch*" OR search="\'search*") AND search_id="*" AND search_id!=rt* AND id!=rt*)
| eval search_id = coalesce(search_id, id)
| rex field=referer "(?P<ref>https?[^\?]+)"
| eventstats latest(ref) as ref by user
| where index="_audit"
| stats latest(ref) as ref min(_time) as _time max(total_run_time) as total_run_time first(search) as search first(apiStartTime) as "Earliest time" first(apiEndTime) as "Latest time" values(host) as host by search_id
Try this. Not sure where you are getting total_run_time
, but this should give you everything else and hopefully a bit faster.
(index=_internal user=* sourcetype=splunkd_ui_access) OR (index=_audit action=search (search="\'rtsearch*" OR search="\'search*") AND search_id="*" AND search_id!=rt* AND id!=rt*)
| eval search_id = coalesce(search_id, id)
| rex field=referer "(?P<ref>https?[^\?]+)"
| eventstats latest(ref) as ref by user
| where index="_audit"
| stats latest(ref) as ref min(_time) as _time max(total_run_time) as total_run_time first(search) as search first(apiStartTime) as "Earliest time" first(apiEndTime) as "Latest time" values(host) as host by search_id
The query seems to executing fast. Thanks
index=audit action=search (id=* OR search_id=) | eval search_id = if(isnull(search_id), id, search_id) | replace '' with * in search_id | search search_id!=rt* | rex "search='(?.?)', autojoin" | convert num(total_run_time) | eval user = if(user="n/a", null(), user) | stats min(_time) as _time first(user) as user max(total_run_time) as total_run_time first(search) as search first(apiStartTime) as "Earliest time" first(apiEndTime) as "Latest time" values(host) as host by search_id | search search_id= search=search* OR search=rtsearch*| sort - total_run_time | fields - search_id | join user [search index=internal user=tk* sourcetype=splunkd_ui_access | rex field=referer "(?Phttps?:\/\/[\w\d./-]+)\?.*"] | dedup search | head 10 | table host user url total_run_time search
i got results but it took long time to execute and i am executing it for last 15 mins . can any one help me out in increase the performance.
Can you edit your post and use the code sample button?
The above doesn't work, I think its missing a few things 🙂
This should start you down the right path:
index=_audit action=search info=granted search=* NOT "search_id='scheduler" NOT "search='|history" NOT "user=splunk-system-user" NOT "search='typeahead" NOT "search='| metadata type=* | search totalCount>0" | stats count by user search _time | sort _time | convert ctime(_time) | stats list(_time) as time list(search) as search by user
thanks but url is missing on which you gave.
i have _time host user total_run_time searchQueryUsed fields in the index=_audit
and url user on index=_internal.
i have joined based on the field name user.
Yeah, it wasnt a complete answer, but a guide in the right direction, teach a man to fish and all that. 🙂