topic Re: Want to merge two query with different fields in one in Splunk Search

Want to merge two query with different fields in one

nikhilup — Wed, 05 Jan 2022 12:17:37 GMT

First query
index = pcf_logs cf_org_name = creorg OR cf_org_name = SvcITDnFAppsOrg cf_app_name=VerifyReviewConsumerService host="*" | eval _raw = msg
| rex "Request\#\:\s*(?<ID1>\d+) with (?<Status>\w+.\w+)"|rex "CRERequestId\"\:\"(?<ID2>[^\"]+)"
| eval ID=coalesce(ID1,ID2)
| stats latest(Status) as Status by ID
| eval Status=trim(Status, "status ")
| stats count by Status

Second query

index = pcf_logs cf_org_name = creorg OR cf_org_name = SvcITDnFAppsOrg cf_app_name=VerifyReviewConsumerService host="*"
| search msg="*Rejected*"
| eval _raw = msg
| rex "(?<CRE_Creation_Date>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}\s..)"
| rex "Request\#\:\s*(?<Rejected_CRE_ID>\d+)"
| rex status(?<Rejected>\s\w+)
| rex (?<Failed_Reason>Rule.*)$
| eval Failed_Reason=trim(Failed_Reason, "Rule ")
| stats count by CRE_Creation_Date Rejected_CRE_ID Rejected Failed_Reason

Re: Want to merge two query with different fields in one

ITWhisperer — Wed, 05 Jan 2022 12:47:35 GMT

Can you give (sanitised) examples of the events you are working with and the aim of the merged query?

Re: Want to merge two query with different fields in one

nikhilup — Wed, 05 Jan 2022 13:04:54 GMT

Actually I want to calculate the friction rate of all the status which I am getting from query

You can see all below status with queries

Manual Review - Splunk Query
------------------------------------------------------
index = pcf_logs cf_org_name = creorg OR cf_org_name = SvcITDnFAppsOrg cf_app_name=VerifyReviewConsumerService host="*"
| search msg="*Manual Review*"
| eval _raw = msg
| rex "(?<CRE_Creation_Date>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}\s..)"
| rex "Request\#\:\s*(?<Manual_CRE_ID>\d+)"
| rex "with(?<Manual_Review>\s\w+\s\w+)"
| rex "(?<Failed_Reason>Rule.*)$"
| eval Failed_Reason=trim(Failed_Reason, "Rule ")
| stats count by CRE_Creation_Date Manual_CRE_ID Manual_Review Failed_Reason

------------------------------------------------------
status Approved - Splunk Query
------------------------------------------------------
index = pcf_logs cf_org_name = creorg OR cf_org_name = SvcITDnFAppsOrg cf_app_name=VerifyReviewConsumerService host="*"
| search msg = "*status Approved*"
| eval _raw = msg
| rex "INFO\s\|\s(?<CRE_Creation_Date>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}\s..)"
| rex "Request\#\:\s*(?<Approved_CRE_ID>\d+)"
| rex "status(?<Approved>\s........)"
| stats count by CRE_Creation_Date Approved_CRE_ID Approved

------------------------------------------------------
status Rejected - Splunk Query
------------------------------------------------------
index = pcf_logs cf_org_name = creorg OR cf_org_name = SvcITDnFAppsOrg cf_app_name=VerifyReviewConsumerService host="*"
| search msg="*Rejected*"
| eval _raw = msg
| rex "(?<CRE_Creation_Date>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}\s..)"
| rex "Request\#\:\s*(?<Rejected_CRE_ID>\d+)"
| rex status(?<Rejected>\s\w+)
| rex (?<Failed_Reason>Rule.*)$
| eval Failed_Reason=trim(Failed_Reason, "Rule ")
| stats count by CRE_Creation_Date Rejected_CRE_ID Rejected Failed_Reason