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

[bmanikya]

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

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.

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

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

[KendallW]

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?

[bmanikya]

Yes

[KendallW]

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]

[bmanikya]

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.

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

If i remove the letter "p"

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.