How can filter top search in a month with timeframe

[taraksinha]

Hello,

How can i display latest dates of searches with time frame,
I need to filter top search in a month, any option to filter, please let me know.

Thanks,

Tarak

[javiergn]

Hi,

Sorry I can't see your last comment but I got the email notification. There are too many nested comments in the answer above so I will reply here.

Yes you can add whatever you want. If you want _time simply include it in your table command but keep in mind you are looking for reports not accessed within the last 2 months so there's no time in reality because we want it to be NULL (not accessed):

| rest /services/saved/searches/
 | search is_visible=1
 | rex field=id "(?<report_uri>[^\/]+$)"
 | join type=left report_uri [
    | search (index=_internal sourcetype=splunkd_ui_access) earliest=-2mon [
         | rest /services/saved/searches/
         | search is_visible=1
         | fields id
         | rex field=id "(?<id>[^\/]+$)"
         | rename id AS query
     ]
     | fields _time, user, clientip, uri_path
     | rex field=uri_path "(?<uri_path>[^\/]+$)"
     | rename uri_path AS report_uri
     | rename _time as searchtime
 ]
 | rename searchtime as _time
 | where isnull(_time)
 | table _time, title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id

If you want to report when those reports were accessed in the last 2 months you need to use the other query I posted above and that already includes a time:

(index=_internal sourcetype=splunkd_ui_access) earliest=-2mon [
     | rest /services/saved/searches/
     | search is_visible=1
     | fields id
     | rex field=id "(?<id>[^\/]+$)"
     | rename id AS query
 ]
 | fields _time, user, clientip, uri_path
 | rex field=uri_path "(?<uri_path>[^\/]+$)"
 | rename uri_path AS report_uri
 | join report_uri [
     | rest /services/saved/searches/
     | search is_visible=1
     | fields title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id
     | rex field=id "(?<report_uri>[^\/]+$)"
 ]
 | table _time, user, clientip, title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id

[javiergn · Accepted solution]

Hi,

Not sure about your question so I'll give a couple of options:

• Search events in the last month using query language:

  yoursearch earliest=-1mon

• Search events in the last month using GUI: click time button on the right from the search GUI and select relative > 1 month

• Top search by month:

  | yoursearch | bucket _time span=1mon | top SOMETHING by _time
  Let me know if that helps otherwise please provide an example of what you are trying to do.

• List all visible reports that were accessed in the last 2 months (explanation provided in nested comments):

  (index=_internal sourcetype=splunkd_ui_access) earliest=-2mon [
  | rest /services/saved/searches/
  | search is_visible=1
  | fields id
  | rex field=id "(?[^\/]+$)"
  | rename id AS query
  ]
  | fields _time, user, clientip, uri_path
  | rex field=uri_path "(?[^\/]+$)"
  | rename uri_path AS report_uri
  | join report_uri [
  | rest /services/saved/searches/
  | search is_visible=1
  | fields title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id
  | rex field=id "(?[^\/]+$)"
  ]
  | table _time, user, clientip, title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id

Thanks,
J

[taraksinha]

Hi JAVIERGN,

I have 1000+ Searches, reports, and alerts in splunk cloud and want to filer last 2 months Top Search and rest of reports will delete.
Let me know if i need to describe more on this.

Thanks,

Tarak

[javiergn]

Hi, I don't have Splunk Cloud so I can't really test this but if you want to filter the last two months of data and then list the top 100 for example, you can do it this way:

yoursearch earliest=-2mon
| top limit=100 listOfFields

If you want a list of your scheduled reports you can probably use the following and play with the different fields in order to find out what you want:

| rest /services/saved/searches

Let me know if that helps.

[taraksinha]

Thank you J

[taraksinha]

Hi J,

How can i search specific reports in search field with owner name?

Thanks,
Tarak

[javiergn]

There's an author field in the searches endpoint so that might be the one you are looking for.
If not, there are 192 fields you can play with and the easiest way to do that is by selecting just one event and transposing the result:

| rest /services/saved/searches
| head 1
| transpose

[taraksinha]

There are 100 reports such as report name " Bill - Monthly Cost", how can i search in search field to filter those reports which are accessed in last 2 months.

I hope u get my point.

[javiergn]

OK, that bit is slightly more complicated but I think I can help on that too:

List all your visible saved searches (that includes reports):

| rest /services/saved/searches/ 
| search is_visible=1
| fields title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id

The id field contains the info you need to use in order to search who's accessing that but it's not usable in the current format so we can apply a quick regex to get just the identifier we need:

| rest /services/saved/searches/ 
| search is_visible=1
| fields title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id
| rex field=id "(?<id>[^\/]+$)"

The above was just to show you how the query looks like and the other info you can retrieve. We just need the id and we need to correlate that with our internal logs in order to find out when was used for the last time.

The following should give you a list of reports that were accessed in the last 2 months. Please test this as my lab is quite small and I might be missing things:

(index=_internal sourcetype=splunkd_ui_access) earliest=-2mon [
    | rest /services/saved/searches/
    | search is_visible=1
    | fields id
    | rex field=id "(?<id>[^\/]+$)"
    | rename id AS query
]
| fields _time, user, clientip, uri_path
| rex field=uri_path "(?<uri_path>[^\/]+$)"
| rename uri_path AS report_uri
| join report_uri [
    | rest /services/saved/searches/
    | search is_visible=1
    | fields title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id
    | rex field=id "(?<report_uri>[^\/]+$)"
]
| table _time, user, clientip, title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id

[taraksinha]

Thanks you so much for your answer, it helped me ,It was really good query.
how can i search now from last 2 months which reports and dashboard was not accessed by anyone?
I want to disable those reports and dashboard, any regex or info. can be use to find those unused reports.

[javiergn]

There's no right join in splunk so simply turn both searches around and use a left join instead.
Something like this:

| rest /services/saved/searches/
| search is_visible=1
| rex field=id "(?<report_uri>[^\/]+$)"
| join type=left report_uri [
   | search (index=_internal sourcetype=splunkd_ui_access) earliest=-2mon [
        | rest /services/saved/searches/
        | search is_visible=1
        | fields id
        | rex field=id "(?<id>[^\/]+$)"
        | rename id AS query
    ]
    | fields _time, user, clientip, uri_path
    | rex field=uri_path "(?<uri_path>[^\/]+$)"
    | rename uri_path AS report_uri
    | rename _time as searchtime
]
| rename searchtime as _time
| where isnull(_time)
| table title, author, is_scheduled, is_visible, eai:acl.perms.write, eai:acl.sharing, search, id

[taraksinha]

I got so much info from your answer, My agenda is to remove unused reports and dashboard from splunk, that's why i am looking for reports and dashboard which were not accessed by anyone in last 2 months. I tried to run your above query but not finding appropriate answer.

I appreciate your stuff which i found and will look forward

[taraksinha]

Is it possible to add _time column in above query . So i can found the date when those reports are accessed?

[taraksinha]

I didn't get any option in dashboard panel to find out oldest dashboard, If you have any idea to search query for dashboard which weren't accessed in last 1 months. Please let me know.

[javiergn]

For dashboards (views) you need to use a different rest call. Try with this one and then play with the output applying the same logic as I did above. Keep in mind all you need to do is to find out when certain URLs have been used and that's something your splunk_ui_access log can tell you.

| rest /servicesNS/-/-/data/ui/views
| table title, author, eai:acl.perms.write, eai:acl.sharing, id

If you look at the id you might be able to filter by app, owner, etc.

[taraksinha]

Hi J,

Your last search query was really good for search,saved search and reports, but may i know how can i filter dashboard as well, we have 200+ dashboard in splunk,

I want to filter those dashboard which weren't accessed in last 1Months.

Thanks,
Tarak