topic Re: Search Query for Splunk Usage in Dashboards & Visualizations

Search Query for Splunk Usage

aditsss — Tue, 06 Oct 2020 21:13:58 GMT

Hi Everyone,

I have one requirement. I have multiple dashboards . I want to calculate the usage of the dashboards based on the search user.

Below are my search's.

SplunkMetadataCounter

JenkinsBuildReport

Extract

......

For individual I am using this query:

index="_internal" SplunkMetadataCounter |stats count by search user.

I am getting the result like this:

search user count

search+index_internal%22+SplunkMetadataCounter kh 1

I want to calculate the dashboard usage on the basis of search and user: I want some query like this not sure this is accurate or not

index="_internal" SplunkMetadataCounter | eval dashboard_name = (if search or sourcetype contains SplunkMetadataCounter ” then dashboard name is“SplunkMetadataCounter 2” else search or sourcetype contains JenkinsBuildReport then dashboard name is“BuildReports ” .................................................) stats count by search user

At the end I want one query which will tell me like SplunkMetadataCounter is used 40 times JenkinsBuildReport is used 20 times like that.

SplunkMetadataCounter -40

JenkinsBuildReport -20

Extract -10

Can someone guide me on this.

Re: Search Query for Splunk Usage

aditsss — Tue, 06 Oct 2020 22:40:16 GMT

Can someone please guide me for splunk usage dashboard query.

Re: Search Query for Splunk Usage

inventsekar — Wed, 07 Oct 2020 01:48:25 GMT

Hi @aditsss .. one warning beforehand... "Do not completely rely on current usage stats of dashboard"..

Regarding the question, this query gives details about which user used which dashboard and how many times.. check it please:

index="_internal" user!="-" sourcetype=splunkd_ui_access "en-US/app" | rex field=referer "en-US/app/(?<app>[^/]+)/(?<dashboard>[^?/\s]+)" | search dashboard!="job_management" dashboard!="dbinfo" dashboard!="*en-US" dashboard!="search" dashboard!="home" dashboard!="alerts" dashboard!="dashboards" dashboard!="reports" dashboard!="report" | bucket _time span=1d | stats dc(dashboard) as c by dashboard user _time

this spl is from @somesoni2 , thanks somesoni!

https://community.splunk.com/t5/Dashboards-Visualizations/Ever-wonder-which-dashboards-are-being-used-and-what-users-are/td-p/262291

Re: Search Query for Splunk Usage

aditsss — Wed, 07 Oct 2020 05:34:34 GMT

@inventsekar

This query is not working for me

index="_internal" user!="-" sourcetype=splunkd_ui_access "en-US/app" 
| rex field=referer "en-US/app/(?<app>[^/]+)/(?<dashboard>[^?/\s]+)" 
| search dashboard!="job_management" dashboard!="dbinfo" dashboard!="*en-US" dashboard!="search" dashboard!="home" dashboard!="alerts" dashboard!="dashboards" dashboard!="reports" dashboard!="report" 
| bucket _time span=1d 
| stats dc(dashboard) as c by dashboard user _time

If I want to see the dashboard usage of only these two dashboards then what query I should follow:

SplunkMetadataCounter

JenkinsBuildReport

I want some query like this:

At the end I want one query which will tell me like SplunkMetadataCounter is used 40 times JenkinsBuildReport is used 20 times like that.

SplunkMetadataCounter -40

JenkinsBuildReport -20

Can someone guide me on this please.

Extract -10

Re: Search Query for Splunk Usage

ITWhisperer — Wed, 07 Oct 2020 07:24:15 GMT

The referrer field is present for events using the ReST interface, so unless you are using that on some form, you probably won't get any results.

index="_internal" user!="-" sourcetype=splunkd_ui_access "en-US/app" | rex "en-US/app/(?<app>[^/]+)/(?<dashboard>[^?/\s]+)" | search dashboard="splunkmetadatacounter" OR dashboard="jenkinsbuildreport" | bucket _time span=1d | stats dc(dashboard) as c by dashboard user _time

I changed the dashboard names to lower case but even then this may not be right. You should look at your browser address bar when you are on the relevant dashboards to see how the dashboard is named in the url and use that.

Re: Search Query for Splunk Usage

aditsss — Wed, 07 Oct 2020 08:31:02 GMT

@ITWhisperer

Its not giving me the correct counts.

I want on the basis of search and user. When I am putting individual like this:

index="_internal" UserLicense| stats count by search user

search count user

search+index%3D%22_internal%22+GfMonitoring-UserLicense 2 a

search+index%3D%22_internal%22+UserLicense 2 a

search+index%3D%22_internal%22+UserLicense%7C+stats+count+by+search 2

search+index%3D%22_internal%22+UserLicense%7C+stats+count+by+search+user 2 a

Below are my logs:

10-07-2020 00:36:36.586 -0700 INFO StreamedSearch - Streamed search connection terminated: search_id=remote_e1_7.3.4_sh_kma__kma_RVBTRl9JbmZyYXN0cnVjdHVyZQ__search7_1602056192.7334, server=ssh, active_searches=12, elapsedTime=0.055, search='litsearch (Name="*" FolderName="*" (index="idx5" OR index="idx3") (sourcetype="Monitoring-UserLicense"))

I want query like this . If search contains "UserLicense" then dashboard name and its count by search user:

index="_internal" UserLicense | eval dashboard_name = (if search or sourcetype contains UserLicense ” then dashboard name is“UserLicense ” else search or sourcetype contains JenkinsBuildReport then dashboard name is“JenkinsBuildReport ” .................................................) stats count by search user.

At the end I want like this how many times dashboard is used:

UserLicense -20

JenkinsBuildReport -40

Can you guide me on this.

Re: Search Query for Splunk Usage

ITWhisperer — Wed, 07 Oct 2020 08:48:06 GMT

The log entry you provided doesn't look to me like the entry you might be interested in - for a start there doesn't appear to be a user field. Do you have some more log entries that demonstrate the events you are looking for?

Re: Search Query for Splunk Usage

aditsss — Wed, 07 Oct 2020 21:32:40 GMT

Hi @ITWhisperer ,

Below are my sourcetypes:

EventLogFiles

Extract

.......

My requirement is like I don't want to extract something . I want to calculate the count how many times dashboard is used by user.

So now if I am running individual's queries for each sourcetype like this:

index="_internal" Extract| stats count by user

I am getting the result like this:

user count

ma20 30

Similarly for the 2nd individual query:

index="_internal" EventLogFiles| stats count by user

user count
ma20 15

Now I want to combine all into one by either using switch statement or If statement and using Eval clause.

Logs for your reference:(Here ma20 is the user)

10.2.116.4 - ma20 [07/Oct/2020:12:29:46.730 -0700] "GET /en-US/splunkd/__raw/services/search/shelper?output_mode=json&snippet=true&snippetEmbedJS=false&namespace=search&search=search+index%3D%22_internal%22+EventLogFiles%7Cstats+count+by+search+user&useTypeahead

Either something like this(Its not accurate)

index="_internal" Infrastructure
| eval DashboardName=if(like(uri, "%EventLogFiles%"), "EventLogFiles",

if(like(uri, "%Extract%"), "Extract",

"Unknown Dashboards"))
| stats count by DashboardName user.

Can you guide me on this so that I can get dashboard name,user and count by using If or swiitch.

@ITWhisperer How can I used multiple if statements here or switch case with eval.

Re: Search Query for Splunk Usage

aditsss — Wed, 07 Oct 2020 21:33:52 GMT

@ITWhisperer Can you please guide me on this. How can I used multiple if statements or switch with eval for below query:

index="_internal" Infrastructure
| eval DashboardName=if(like(uri, "%EventLogFiles%"), "EventLogFiles",

if(like(uri, "%Extract%"), "Extract",

"Unknown Dashboards"))
| stats count by DashboardName user.

Your solutions always works for me please help me out.

Re: Search Query for Splunk Usage

ITWhisperer — Wed, 07 Oct 2020 21:25:26 GMT

index="_internal" Infrastructure | eval DashboardName=case(like(uri, "%EventLogFiles%"), "EventLogFiles", like(uri, "%Extract%"), "Extract", true(), "Unknown Dashboards") | stats count by DashboardName user

Re: Search Query for Splunk Usage

aditsss — Wed, 07 Oct 2020 22:14:33 GMT

@ITWhisperer

This is not working for me.

index="_internal" Infrastructure
| eval DashboardName=case(like(uri, "%EventLogFiles%"), "EventLogFiles", like(uri, "%Extract%"), "Extract", true(), "Unknown Dashboards")
| stats count by DashboardName user

Not getting the correct counts.Its only showing the EventLogFiles and unknown dashboards(Counts are not correct) not the Extract dashboards count.

I tried with append not sure its correct or not.

index="_internal" EventLogFiles
| eval DashboardName=if(like(uri, "%EventLogFiles%"), "EventLogFiles", "Unknown Dashboards")
| stats count by DashboardName user|append[search index="_internal" Extract
| eval DashboardName=if(like(uri, "%Extract%"), "Extract", "Unknown Dashboards")
| stats count by DashboardName user].

How can I do it without using append and get correct counts.

Re: Search Query for Splunk Usage

aditsss — Thu, 08 Oct 2020 06:35:20 GMT

@ITWhisperer

I treid with below script.

index="_internal" Infrastructure
| eval DashboardName=if(like(uri, "%EventLogFiles%"), "EventLogFiles",
if(like(uri, "%JenkinsBuildReport%"), "JenkinsBuildReport",JenkinsBuildReport",if(like(uri,"%OrgHealthCheck%"), "OrgHealthCheck",
"Unknown Dashboard")))|stats count by DashboardName user

DashboardName user count
JenkinsBuildReport ma19 48
SalesforceEventLogFiles math 2
Unknown Dashboard runel 643
Unknown Dashboard ma19 2622

Its not showing the 3rd one OrgHealthCheck count with user . Nor its showing the correct count event log files and Jenkins build report. Where I have gone wrong.

Can you guide me.

Re: Search Query for Splunk Usage

ITWhisperer — Thu, 08 Oct 2020 07:05:01 GMT

Please repost the exact query (preferably in a code block) as there appears to have been some pasting errors.

Re: Search Query for Splunk Usage

aditsss — Thu, 08 Oct 2020 07:17:09 GMT

@ITWhisperer

index="_internal" Infrastructure
| eval DashboardName=if(like(uri, "%EventLogFiles%"), "EventLogFiles",
if(like(uri, "%JenkinsBuildReport%"), "JenkinsBuildReport",if(like(uri,"%OrgHealthCheck%"), "OrgHealthCheck",
"Unknown Dashboard")))|stats count by DashboardName user

DashboardName user count
JenkinsBuildReport ma19 48
SalesforceEventLogFiles math 2
Unknown Dashboard runel 643
Unknown Dashboard ma19 2622

Its not showing the 3rd one OrgHealthCheck count with user . Nor its showing the correct count for event log files and Jenkins build report. Where I have gone wrong.

Can you guide me.