@ITWhisperer @richgalloway Thank you for those suggestions. Actually, the requirement has just changed and we want to see if the policy_name status changed from X(A) to X. So, I tried the following query to get the results from Today and Last 48 hours to compare and see how many of them changed from X(A) to X. index=xyz sourcetype=abc earliest=-48h@h latest=@d-1m
| search (policy_name="*-X" OR policy_name="*-X(A)")
| rex field=policy_name "(?<namePattern>([^-]+\-){5})(?<State>[^-]+)"
| rename namePattern as oldPattern
| stats count by _time, policy_name, oldPattern, State
| append
[search index=xyz sourcetype=abc earliest=@d latest=now
| search (policy_name="*-X" OR policy_name="*-X(A)")
| rex field=policy_name "(?<namePattern>([^-]+\-){5})(?<State>[^-]+)"
| rename namePattern as newPattern
| stats count by _time, policy_name, newPattern, State ]
| table _time, policy_name, newPatternm oldPattern, State Now the results from this query are in the below format. I'm trying to compare the State field values based on newPattern and oldPattern of the policy_name field values to see which of them are changed from X(A) to X and get the list of changed policies ending in state X. But, as they are on different rows, I'm unable to compare them or I'm not sure how to compare them. Ex: _time policy_name newPattern oldPattern State 2024-08-27 13:00:06.827 policy_1_X(A) policy_1_ X(A) 2024-08-27 13:00:06.827 policy_2_X(A) policy_2_ X(A) 2024-08-28 06:31:24.775 policy_1_X policy_1_ X 2024-08-28 06:31:24.775 policy_2_X policy_2_ X 2024-08-29 10:57:25.000 policy_3_X(A) policy_3_ X(A) 2024-08-29 10:57:25.000 policy_4_X(A) policy_4_ X(A) 2024-08-29 11:57:25.000 policy_3_X policy_3_ X 2024-08-29 11:57:25.000 policy_4_X policy_4_ X changed_policies policy_1_X policy_2_X policy_3_X policy_4_X
... View more