Splunk Search
Highlighted

How to compare fields value with old fields value

New Member

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.

0 Karma
Highlighted

Re: How to compare fields value with old fields value

Explorer

[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

0 Karma
Highlighted

Re: How to compare fields value with old fields value

New Member

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 ?

0 Karma
Highlighted

Re: How to compare fields value with old fields value

Super Champion

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.

Highlighted

Re: How to compare fields value with old fields value

New Member

I tried using this search:
index=xxx sourcetype= xxxx
| fields *
| table time LATITUDE LONGITUDE CELL NAME ADDRESS BAND PCI SITEELEV ECELLID 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 PrevLAT 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.

0 Karma
Highlighted

Re: How to compare fields value with old fields value

Super Champion

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
0 Karma
Highlighted

Re: How to compare fields value with old fields value

Motivator

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

Cheers,
Jacob
0 Karma
Highlighted

Re: How to compare fields value with old fields value

New Member

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.

0 Karma
Highlighted

Re: How to compare fields value with old fields value

New Member

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

0 Karma
Highlighted

Re: How to compare fields value with old fields value

New Member

index=XXXsourcetype= XXX
| fields whenChanged *
| table time whenChanged LATITUDE LONGITUDE CITY SITEID ENODEBID CELLID BAND CELLNAME GROUNDELEVATIONAMSL
| dedup CELL
NAME sortby time
| sort CELL
NAME - whenChanged
| fillnull value="N/A"
| streamstats count by CELLNAME
| where count < 3
| fields - count
| transaction maxevents=2 mvlist=true CELL
NAME
| eval "Updated Fields" = "NEWCHANGES"
| foreach *
[ eval "Updated Fields" = if (mvcount('CELL
NAME') = 2,
if (mvindex('CELLNAME', 0) = mvindex('CELLNAME', 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.

0 Karma