Analysis on splunk users

[sravankaripe]

Analysis on splunk users, for this i need to display

_time host user total_run_time searchQueryUsed Url

Please help me with splunk query

[sundareshr · Accepted solution]

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

[sravankaripe]

The query seems to executing fast. Thanks

[sravankaripe]

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.

[mrgibbon]

Can you edit your post and use the code sample button?
The above doesn't work, I think its missing a few things 🙂

[mrgibbon]

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

[sravankaripe]

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.

[mrgibbon]

Yeah, it wasnt a complete answer, but a guide in the right direction, teach a man to fish and all that. 🙂