How to filter application search associated with user and owner?

[vinodsinha]

Hi Team,

I want to filter Application by search with there owner of reports, search or dashboard. and who is not accessed in 60 days?

Thanks,

[javiergn]

Hi,

Your question is very vague. Can you provide more details please?
What exactly are you trying to achieve?

If you just want to list reports or dashboards not accessed in the the last 60 days then take a look at this:

https://answers.splunk.com/answers/363829/how-can-filter-top-search-in-a-month-with-timefram.html

If you want to find out who ran what search:

https://answers.splunk.com/answers/369183/splunk-searches-run-by-user.html

Thanks,
J

UPDATE:

Final query is as follows:

index=_internal sourcetype=splunkd_ui_access earliest=-1y
 | rex field=uri "\/app\/(?<AppName>[\w-]+)\/(?<ViewName>[\w-\.]+)"
 | table _time, AppName, ViewName, user
 | search AppName=search AND ViewName=*
 | eval ViewName = if(match(ViewName, "\."), AppName, ViewName)
 | stats max(_time) as LastAccessed, last(user) as user by AppName, ViewName
 | eval age=round((now()-LastAccessed)/86400)
 | where age > 60
 | eval Date=strftime(LastAccessed,"%d-%m-%Y %H:%M:%S") 
 | join type=left ViewName [
    | rest /servicesNS/-/-/data/ui/views 
    | table author title 
    | rename title as ViewName author as owner]
 | table AppName, ViewName, Date, age, owner, user

[vinodsinha]

Hi J,

I am looking at a similar search along with user field?

index=_internal source=*access.log earliest=-2mon  */app/* | rex "\/app\/(?\w+)\/(?\w+)\"" | search AppName=search AND ViewName=* | stats max(_time) as LastAccessed by AppName, ViewName | eval age=now()-LastAccessed | where age> 20 |eval Date=strftime(LastAccessed,"%d-%m-%Y %H:%M:%S") | join type=left ViewName  [| rest /servicesNS/-/-/data/ui/views | table author title | rename title as ViewName author as owner ]

[javiergn]

Hi,

Can you paste your query again but using the code button instead (the one with 1s and 0s)?
Otherwise any special or reserved characters will be either omitted or behave differently.

Based on your query above I'm guessing you want to list Views not accessed in the last 20 days so I've made some modifications/fixes to your query and this is the result:

index=_internal sourcetype=splunkd_ui_access earliest=-2mon
| rex field=uri "\/app\/(?<AppName>[\w-]+)\/(?<ViewName>[\w-\.]+)"
| table _time, AppName, ViewName
| search AppName=search AND ViewName=*
| eval ViewName = if(match(ViewName, "\."), AppName, ViewName)
| stats max(_time) as LastAccessed by AppName, ViewName
| eval age=round((now()-LastAccessed)/86400)
| where age > 20
| eval Date=strftime(LastAccessed,"%d-%m-%Y %H:%M:%S") 
| join type=left ViewName [
   | rest /servicesNS/-/-/data/ui/views 
   | table author title 
   | rename title as ViewName author as owner]
| fields - LastAccessed

Output:

AppName     ViewName    Date    age     owner
search  job_management  01-02-2016 21:22:33     21  nobody
search  search  01-02-2016 14:39:22     21  nobody 

If this is not what you are trying to achieve please post an output example. Something like:

# My expected output
AppName, ViewName, LastAccessed, Date, age, owner
X, Y, Z, 1, 2, 3

[vinodsinha]

Hi J,

I missed to check your reply, It's look good which i was looking for the "Search" App name Item which are not accessed in the last 60 days along with user field, so i can find out the owner who created the search Item and user who accessed the search item earlier(60 days ago).

Can you describe me the output for below command in query:-

| eval age=round((now()-LastAccessed)/86400)
| where age > 20

[javiergn]

Sure.

Both now() and LastAccessed are in epoch format (seconds basically) so you need to divide them by 86400 seconds we have every day in order to display age in days.
Then the where clause is just filtering by those events where age is greater than 20 days.

Hope that makes sense.

Based on your last comment I'm assuming the answer below is doing what you want so please confirm if that's the case.

Thanks,
J

[vinodsinha]

Hi J,

I want these below field in my output such as :-

App_Name:- search
view_name:- anything
Date:-
age:- last 60 days
Owner:- who created search item
User:- who last time accessed those search item

Similar output like this:-
AppName ViewName Date age owner user
search job_management 01-02-2016 21:22:33 21 nobody tarak

Please let me know if any other details which i can provide you.
Seems i am near to my solution.

Thanks for your time to reply useful info.

[javiergn]

Hi,

Then simply add the user field to your first initial filter.
Take a look at the following, it will query your logs for the last year but only return those where age is greater than 60 days. Feel free to play with the earliest and age values but the output is similar to the one you posted above. I have also updated my first answer.

index=_internal sourcetype=splunkd_ui_access earliest=-1y
 | rex field=uri "\/app\/(?<AppName>[\w-]+)\/(?<ViewName>[\w-\.]+)"
 | table _time, AppName, ViewName, user
 | search AppName=search AND ViewName=*
 | eval ViewName = if(match(ViewName, "\."), AppName, ViewName)
 | stats max(_time) as LastAccessed, last(user) as user by AppName, ViewName
 | eval age=round((now()-LastAccessed)/86400)
 | where age > 60
 | eval Date=strftime(LastAccessed,"%d-%m-%Y %H:%M:%S") 
 | join type=left ViewName [
    | rest /servicesNS/-/-/data/ui/views 
    | table author title 
    | rename title as ViewName author as owner]
 | table AppName, ViewName, Date, age, owner, user

[vinodsinha]

Not getting any result from above query.....

[javiergn]

Hi, the query above works fine when I run it.
As i said before, you have to play with the values for age and earliest.
If the above doesn't work it probably means there are now views where age is greater than 60 days old in the last year (earliest=-1y)

[vinodsinha]

Hi Team,

Any answer for above issue?