How to compare the field values from last 48 hours with field values from today

[shashank9]

Hi Splunkers,

I'm trying to compare the policy names from Today with policy names from past 48 hours to see if there is any change in policy names. I tried using append as well as join to compare the results from last 48 hours with Today's timeframe. But, I'm unable to get the expected output or result.

Ex: In the below table I'm trying to see if there are any changes in policy names from last 48 hours. So, policy_3_sf's name is changed to policy_3_sk. Similarly, policy_4_sg and policy_5_gh names are changed to policy_4_sp and policy_5_gk respectively and are the new names I would like to list through my query as per the requirement.

Last_48_Hours_Policy_Names	Today_Policy_Names	New_Policy_Names
policy_1_xx	policy_1_xx
policy_2_xs	policy_2_xs
policy_3_sf	policy_3_sk	policy_3_sk
policy_4_sg	policy_4_sp	policy_4_sp
policy_5_gh	policy_5_gk	policy_5_gk

Could you please let me know if my approach is correct or if something is missing in my queries?

Thanks,

[shashank9]

@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

[ITWhisperer]

Please clarify what you expect - your example shows policy_3 and policy_4 changing in the last 24 hours by the removal of (X) not the addition, and they don't appear prior to today, so what is it that you are trying to compare. Similarly, policy_1 and policy_2 do not appear today, although they do appear to have changed by the removal of (X) within the 48 hours prior to today.

[richgalloway]

Putting like events together on the same line is the purpose of step #7 in my original reply.  Doing that, however, requires a field with values common to both the old and new policies.

[ITWhisperer]

Try something like this

| eval root=mvjoin(mvindex(split(policy,"_"),0,1),"_")
| eval version=mvindex(split(policy,"_"),2)
| timechart span=48h values(version) as version by root
| eval date=if(_time < relative_time(now(),"-2d"), "Last 48 Hours", "Today")
| fields - _time _span
| transpose 0 header_field=date column_name=policy
| eval "New version"=if('Last 48 Hours' == Today, null(), Today)

[richgalloway]

Here's the approach I would use.  It may not be the best way.

1. Search the last 48 hours for the desired events
2. Extract the Policy_Name field into Last_48_Hours_Policy_Names
3. Extract the "root" policy name ("policy_n_") from Last_48_Hours_Policy_Names
4. Append the search of today for the desired events
5. Extract the Policy_Name field into Today_Policy_Names
6. Extract the "root" policy name ("policy_n_") from Today_48_Hours_Policy_Names
7. Regroup the results on the root policy name field
8. Discard the root policy name field
9. Compare Last_48_Hours_Policy_Names to Today_48_Hours_Policy_Names.  If different, set New_Policy_Names to Today_Policy_Names