topic Re: How to compare fields value with old fields value in Splunk Search

How to compare fields value with old fields value

kulwindersandhu — Thu, 10 Oct 2019 16:25:01 GMT

I am trying to make a search that will compare the fields value with the old fields value to determine if there is any change in the value over time or if there is a new field added. For example, say I have 5 fields | fields _time Location Band CellID CellName. I want to run this search and compare the these 5 fields data with a week old data of same fields. Say, Week 1 these fields show values as :

_time Location Band CellID CellName
2019-10-10 California 700 123 CELL123456-700-1

Now I want to see if the last week the values were the same or different for same fields.
Please help me to build the logic. Thanks in advance.

Re: How to compare fields value with old fields value

rlippincott — Thu, 10 Oct 2019 19:02:05 GMT

[insert normal search criteria here] | eval didItChange=(case(CellName != value, "Changed"))| stats latest(CellName) as CellName | table CellName, didItChange

You can do this with the eval statement. Inside the eval, you can try using an if statement if you want instead of a case statement. If statements have acted wonky for me before.

https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/CommonEvalFunctions
https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/SearchTimeModifiers

Re: How to compare fields value with old fields value

cmerriman — Thu, 10 Oct 2019 19:10:47 GMT

you can use streamstats

something like

<base search that gives a table of weekly information sorted properly>
|streamstats window=1 current=f values(*) as prev_* by _time

may need to adjust label or sorting to have it make sense, though.

Re: How to compare fields value with old fields value

jacobpevans — Thu, 10 Oct 2019 19:53:33 GMT

Greetings @kulwindersandhu,

If you're ready to go for a ride, I've got a fancy search to display on one line all of the Active Directory fields that changed and the time of both the last change and the change before that.

Base search:

index=active_directory
| table _time whenChanged Name Enabled LockedOut PasswordExpired

Sample Base Data:

_time                 whenChanged            Name     Enabled LockedOut PasswordExpired
2019-10-10 05:15:08 2019-10-10 02:15:08 evansj  false   true      false
2019-10-09 05:15:06 2019-10-09 03:15:06 evansj  true    false     false
2019-10-08 05:15:09 2019-10-08 04:15:06 evansj  true    false     false

Full search:

index=active_directory
| table _time whenChanged Name Enabled LockedOut PasswordExpired
| dedup Name whenChanged sortby -_time
| sort     - whenChanged
| fillnull value="N/A"

| streamstats count by Name
| where count < 3
| fields - count
| transaction maxevents=2 mvlist=true Name

| eval "Updated Fields" = ""
| foreach *
    [ eval "Updated Fields" = if (mvcount('<<FIELD>>') = 2,
                                      if (mvindex('<<FIELD>>', 0) = mvindex('<<FIELD>>', 1),
                                              'Updated Fields',
                                              'Updated Fields' . "<<FIELD>>, "),
                                      'Updated Fields') ]

| eval "Most Recent AD Change Time" = mvindex('whenChanged',1)
| eval "Previous AD Change Time"    = mvindex('whenChanged',0)

| foreach * 
    [ eval <<FIELD>> = mvindex('<<FIELD>>', -1) ]

| search "Updated Fields" != ""

| table "Previous AD Change Time" "Most Recent AD Change Time" "Updated Fields" Name Enabled LockedOut PasswordExpired
| fillnull value="N/A"

Dissecting the search
Get all unique combinations of Name and whenChanged

index=active_directory
| table _time whenChanged Name Enabled LockedOut PasswordExpired
| dedup Name whenChanged sortby -_time
| sort     - whenChanged
| fillnull value="N/A"

Get the two most recent events by Name, and concatenate them using transaction so that there is now one event per name with a multivalue list of all fields. mvindex(1) is the more recent value for all fields and mvindex(0) is the previous value before that.

| streamstats count by Name
| where count < 3
| fields - count
| transaction maxevents=2 mvlist=true Name

This part is the magic. Create a new field called "Updated Fields". Iterate over all the fields in your table. If the two values in the multivalue are identical, keep "Updated Fields" the same. If they differ, append the name of that field to "Updated Fields". Because I know that whenChanged must have changed (or probably _time in your query), I can set two new fields knowing which is the more recent and which is the previous value.

| eval "Updated Fields" = ""
| foreach *
    [ eval "Updated Fields" = if (mvcount('<<FIELD>>') = 2,
                                      if (mvindex('<<FIELD>>', 0) = mvindex('<<FIELD>>', 1),
                                              'Updated Fields',
                                              'Updated Fields' . "<<FIELD>>, "),
                                      'Updated Fields') ]

| eval "Most Recent AD Change Time" = mvindex('whenChanged',1)
| eval "Previous AD Change Time"    = mvindex('whenChanged',0)

I don't care what the older values are anymore. You don't need this part if you want to show both values. This just retrieves the most recent value for each field (only for the multivalue fields).

| foreach * 
    [ eval <<FIELD>> = mvindex('<<FIELD>>', -1) ]

If nothing has changed, I'm not interested so discard the event.

| search "Updated Fields" != ""

Sample Output:

Previous AD Change Time  Most Recent AD Change Time  Updated Fields                         Name    Enabled LockedOut PasswordExpired
2019-10-09 03:15:06      2019-10-10 02:15:08         Last AD Change Time, Enabled, LockedOut,  evansj  false   true      false

Cheers,
Jacob

Re: How to compare fields value with old fields value

kulwindersandhu — Wed, 30 Sep 2020 02:26:16 GMT

I tried using this search:
index=xxx sourcetype= xxxx
| fields *
| table time LATITUDE LONGITUDE CELL NAME ADDRESS BAND PCI SITE_ELEV ECELL_ID PSITECODE
|streamstats window=1 current=f values(*) as prev* by _time

I get around 10,000 results as this is a big file and it gets updated every week, I just want to see the changes happened over last week or over last month.

Also the result in statistics needs little tweaking, look below:
_time LAT LONG BAND PCI ID ECELL NAME Prev_LAT Prev_LONG BAND PCI ID ECELL NAME
1 2019-10-10 06:06:02 49.265242 -96.512477 700 8 305 103 ZS

2 2019-10-10 06:06:02 49.265242 -96.512477 700 7 305 102 ZS 49.265242 -96.512477 700 8 305 103 ZS

As you can see row one and its previous result show up on row two and it continues like this:

1
2 1
3 2
4 3

Also how would I know how old is this previous data. Please help to resolve this.

Re: How to compare fields value with old fields value

kulwindersandhu — Thu, 10 Oct 2019 20:34:54 GMT

Hey Jacob, I really appreciate your response. Kindly give me few hours to understand this answer. As I am fairly new to splunk. I'll update once I could come out with some result.

Re: How to compare fields value with old fields value

kulwindersandhu — Thu, 10 Oct 2019 20:37:56 GMT

I tried the search, it adds a column diditchange to the table, which shows same or not same. I need to know how old is the data it is comparing with ?

Re: How to compare fields value with old fields value

cmerriman — Fri, 11 Oct 2019 01:15:37 GMT

you know what....streamstats by _time might be a terrible solution for this because you need to compare two different times...sorry...

this is what you should try:

index=foo|stats count as _count by _time LATITUDE LONGITUDE CELL NAME ADDRESS BAND PCI SITE_ELEV ECELL_ID PSITECODE|timewrap 1w

Re: How to compare fields value with old fields value

woodcock — Mon, 14 Oct 2019 00:28:04 GMT

Like this:

index=YouShouldAlwaysSpecifyAnIndex sourcetype=AndSourcetypeToo (earliest=-1w@d latest=-1w+1d@d OR earliest=@d latest=now)
| rename sourcetype AS Location date_hour AS Band host AS CellID component AS CellName 
| bin _time span=1d 
| fields Location Band CellID CellName 
| stats first(*) AS * BY _time
| stats dc(_time) AS count BY Location Band CellID CellName
| where count==1

Re: How to compare fields value with old fields value

kulwindersandhu — Mon, 21 Oct 2019 19:29:45 GMT

Hey Jacob, I am trying your suggestion. I want to work extensively on this, would you be available anytime soon to help.

Re: How to compare fields value with old fields value

kulwindersandhu — Wed, 30 Sep 2020 02:35:08 GMT

index=XXXsourcetype= XXX
| fields whenChanged *
| table _time whenChanged LATITUDE LONGITUDE CITY SITE_ID ENODEB_ID CELL_ID BAND CELL_NAME GROUND_ELEVATION_AMSL
| dedup CELL_NAME sortby _time
| sort CELL_NAME - whenChanged
| fillnull value="N/A"
| streamstats count by CELL_NAME
| where count < 3
| fields - count
| transaction maxevents=2 mvlist=true CELL_NAME
| eval "Updated Fields" = "NEW_CHANGES"
| foreach *
[ eval "Updated Fields" = if (mvcount('CELL_NAME') = 2,
if (mvindex('CELL_NAME', 0) = mvindex('CELL_NAME', 1),
'Updated Fields',
'Updated Fields' . "CELL_NAME, "),
'Updated Fields') ]
| eval "Most Recent AD Change Time" = mvindex('whenChanged',1)
| eval "Previous AD Change Time" = mvindex('whenChanged',0)

This is what I used, it just shows all parameters with a new field whenChanged. I want to see new additions and changes and I am not able to see them. Please help.