Re: Search Query for Splunk Usage

aditsss · ‎10-06-2020

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.

inventsekar · ‎10-06-2020

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-use...

aditsss · ‎10-06-2020

@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:

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

Can someone guide me on this please.

Extract -10

ITWhisperer · ‎10-07-2020

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.

aditsss · ‎10-07-2020

@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.

ITWhisperer · ‎10-07-2020

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?

aditsss · ‎10-06-2020

Can someone please guide me for splunk usage dashboard query.

aditsss · ‎10-07-2020

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.

ITWhisperer · ‎10-07-2020

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

aditsss · ‎10-07-2020

@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.

ITWhisperer · ‎10-08-2020

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

aditsss · ‎10-08-2020

@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.

aditsss · ‎10-07-2020

@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.

aditsss · ‎10-07-2020

@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.

Search Query for Splunk Usage

panel

simple XML

Index This | Forward, I’m heavy; backward, I’m not. What am I?

A Guide To Cloud Migration Success

Join Us for Splunk University and Get Your Bootcamp Game On!