Splunk Search

Need to send email notifications to the users and service-ids who are utilizing CPU.

bmanikya
Loves-to-Learn Lots
index=XXX sourcetype=XXX [|inputlookup Edge_Nodes_All.csv where Environment="*" AND host="*" |fields host] |fields cluster, host, user, total_cpu
| join type=inner host [search `gold_mpstat` OR `silver_mpstat` OR `platinum_mpstat` OR `palladium_mpstat` [|inputlookup Edge_Nodes_All.csv where Environment="*" AND host="*" |fields host]
|stats max(eval(id+1)) as cores by host]
|eval pct_CPU = round(total_cpu/cores,2)
|stats max(total_cpu) as total_cpu, max(pct_CPU) as "CPU %" by user,host,cores
|table host user cores total_cpu,"CPU %"
|sort - "CPU %"|head 10

bmanikya_0-1718190578365.png

 

If you can look at the above screenshot, from the second column we have ADS-IDs and service-IDS mostly end up with s,g,p according to our environments like silver, gold and platinum. We have ADS-IDS in |  bd_users_hierarchy.csv lookup file, please check below screenshot.(Note: for security reasons, have to grayed out email addresses.

bmanikya_1-1718190828938.png

And service-IDS are in the below index, please check below screenshot

index = imdc_ops_13m sourcetype = usecase_contact app_id="*" | dedup app_id | table _time app_id app_owner app_team_dl

bmanikya_2-1718190933711.png

 

 

I was using subsearch using join but not successful. Any help is appreciated.

 

0 Karma

KendallW
Communicator

Hi @bmanikya please confirm if my understanding is correct:
You want to match the "user" field from the first screenshot with the "user" field from the bd_users_hierarchy.csv lookup, and the "app_id" field from the third screenshot?

0 Karma

bmanikya
Loves-to-Learn Lots

Yes

0 Karma

KendallW
Communicator

Try something like this:

 

index=XXX sourcetype=XXX 
	[|inputlookup Edge_Nodes_All.csv where Environment="*" AND host="*" 
	 |fields host] 
|fields cluster, host, user, total_cpu
| join type=inner host [search `gold_mpstat` OR `silver_mpstat` OR `platinum_mpstat` OR `palladium_mpstat` [|inputlookup Edge_Nodes_All.csv where Environment="*" AND host="*" |fields host]
|stats max(eval(id+1)) as cores by host]
|eval pct_CPU = round(total_cpu/cores,2)
|stats max(total_cpu) as total_cpu, max(pct_CPU) as "CPU %" by user,host,cores
|table host user cores total_cpu,"CPU %"
|sort - "CPU %"|head 10

| lookup bd_users_hierarchy.csv user OUTPUT user, email as user_email, UserName, Director, VP,Director_email,VP_email

| join left=L right=R where L.user=R.app_id [index = imdc_ops_13m sourcetype = usecase_contact app_id="*" | dedup app_id | table _time app_id app_owner app_team_dl]

 

 

0 Karma

bmanikya
Loves-to-Learn Lots

No results after executing the query. There is a lookup file called "bd_users_hierarchy.csv" which contains Active Directory users and "mapr_ticket_contacts.csv " where in UseCase information exists. Please check below screenshot and query i have written to find out Top CPU Users and Usecases on all edge nodes.

bmanikya_0-1719286381276.png

 

In the inputlookup file called ""mapr_ticket_contacts.csv", Usecases ends with letter "s,q,g,p" need to trim down and get email addresses. For example

bmanikya_2-1719287002129.png

If i remove the letter "p"

bmanikya_3-1719287045727.png

 

Edge Node Information  --- Edge_Nodes_All.csv

Active Directory Users  --- bd_users_hierarchy.csv

UseCases -- mapr_ticket_contacts.csv ( Need to trim down letter "s,q,g,p")

 

I have tried with the below splunk query, but not getting results

index=imdc_*_os sourcetype=ps1 [|inputlookup Edge_Nodes_All.csv where Environment="*" AND host="*" |fields host] |fields cluster, host, user, total_cpu
| join type=inner host [search `gold_mpstat` OR `silver_mpstat` OR `platinum_mpstat` OR `palladium_mpstat` [|inputlookup Edge_Nodes_All.csv where Environment="*" AND host="*" |fields host]
|stats max(eval(id+1)) as cores by host]
|eval pct_CPU = round(total_cpu/cores,2)
|stats max(total_cpu) as total_cpu, max(pct_CPU) as "CPU %" by user,host,cores
|table host user cores total_cpu,"CPU %"
| search NOT user IN ("root","imdcsup","hadpsup")
|sort - "CPU %"|head 10
| join type=left user
[| inputlookup bd_users_hierarchy.csv| rename email as user_email
| table user,user_email]
| join type=left user
[| inputlookup mapr_ticket_contacts.csv
| eventstats max(Modified_Time) as Modified_Time_max by UseCase
| where Modified_Time=Modified_Time_max
| eval Modified_Time=if(Modified_Time=0,"Not Updated",strftime(Modified_Time,"%Y-%m-%d %H:%M"))
| rename Updated_By as "Last_Updated_By",Modified_Time as "Last_Modified_Time"
| rex field=UseCase "(?<UseCase>.*)."  
| rename UseCase as user
| rename Support_Team_DL as user_email
| table user,user_email]

Appreciate your quick response on the same.

 

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...