topic Re: How to compare field values in time? in Splunk Search

How to compare field values in time?

batsonpm — Sun, 06 May 2018 02:54:11 GMT

We are gathering data on information tags on servers. We want to know when a specific tag value changes so that we can send an alert. This is a db that is read into splunk every 30 minutes. I want to search through the data, and find the relevant data when the field changes and send an alert. Unfortunately, the source is not rising, it is batch.

I'm really new to splunk, so i'm still trying to figure out which function I would use for this.

Thanks for any assistance!

Re: How to compare field values in time?

xpac — Sun, 06 May 2018 08:37:39 GMT

You would make both our lifes a lot easier if you could provide some example data and a mockup of what you would like your results to look like 😉

Re: How to compare field values in time?

woodcock — Sun, 06 May 2018 18:29:31 GMT

I don't know what you mean here. Tags don't change unless admins change them. I significant rephrasing with much more detail will get you more people willing and able to help.

Re: How to compare field values in time?

batsonpm — Mon, 07 May 2018 01:02:29 GMT

Yes, I understand. Sorry, first time trying to ask a question here about an app that I'm unfamiliar with. Wasn't sure what info was required, and I can't just dump company info on a public website.
On a side note, I think I may have figured out one problem. Seems I was missing quotes around field names again. I honestly don't know how developers keep syntax straight between so many apps. Its maddening.

So let me start over:

We are pulling tags from AWS servers. We want to be notified when a tag changes. The data is put into Splunk via batch, so we have to search and find the 2nd to last entry in the data and compare it to the newest to see if the tag changes.

The following kind of works, and I think I'm on the right track, but not positive.

stats first("configurationItem.tags.awstagGroup") as previous, last("configurationItem.tags.awstagGroup") as current by "configurationItem.tags.Name"

From what I have been reading, I think "first" is grabbing the data from the first entry of the table on the "configurationItem.tags.Name" field. So if anything changes after that first entry, it would show the wrong data with that query, and not the second to last entry of the "configurationItem.tags.Name" field. I'm not sure how to compare to the 2nd to last entry in the table. And how to display the _time that it was changed.

I hope that is a little clearer. Sorry for the confusion and lack of information.

Re: How to compare field values in time?

batsonpm — Mon, 07 May 2018 01:09:37 GMT

Output would be :

Name tag Previous Tag Current Tag Time Changed
Servername group4 group5 5/6/18 12:13:00

Re: How to compare field values in time?

batsonpm — Mon, 07 May 2018 01:14:49 GMT

My first comment back to you is being reviewed by the mods. So, sorry if you see the "output" comment before the first one.

Re: How to compare field values in time?

xpac — Mon, 07 May 2018 16:46:47 GMT

You can check this to make sure you post a "good" question 😉
http://docs.splunk.com/Documentation/Splunkbase/splunkbase/Answers/Questions

Is your AWS data pulled with a fixed intervall?

Re: How to compare field values in time?

batsonpm — Tue, 29 Sep 2020 19:22:13 GMT

Thank you for that link!

I don't have access to the index as it is from another part of the company that is using splunk. I just have access to the data. But, from what I see in the data, it is pulled every 30 minutes.

This is what I have been working with this morning. Seems to be closer, but I'm getting data that still has the same info in the tag.

| streamstats current=f last("configurationItem.tags.OnPremPatchGroup") as oldPatchGrp last(_time) as time_of_change by "configurationItem.tags.Name"
| where "configurationItem.tags.OnPremPatchGroup" != oldPatchGrp | convert ctime(time_of_change) as time_of_change | table time_of_change "configurationItem.tags.Name" oldPatchGrp configurationItem.tags.OnPremPatchGroup | sort +"configurationItem.tags.Name" +time_of_change

time_of_change configurationItem.tags.Name oldPatchGrp configurationItem.tags.OnPremPatchGroup
05/06/2018 19:53:57.038 ASAAWSTEST01 GROUP2620 GROUP2614
05/06/2018 20:27:34.912 ASAAWSTEST01 AWS_EC2_AUTOPATCHING GROUP2620
05/07/2018 08:18:34.444 ASAAWSTEST01 GROUP2604 AWS_EC2_AUTOPATCHING
05/06/2018 12:43:19.964 ASAAWSTEST02 Group2617 Group2614
05/06/2018 13:17:50.563 ASAAWSTEST02 Group2617 Group2617
05/06/2018 13:48:29.774 ASAAWSTEST02 Group2617 Group2617
05/06/2018 14:18:45.165 ASAAWSTEST02 Group2617 Group2617
05/06/2018 14:48:55.298 ASAAWSTEST02 Group2617 Group2617
05/06/2018 15:18:30.275 ASAAWSTEST02 Group2617 Group2617
05/06/2018 15:49:05.525 ASAAWSTEST02 Group2617 Group2617
05/06/2018 16:20:02.813 ASAAWSTEST02 Group2617 Group2617
05/06/2018 16:49:07.123 ASAAWSTEST02 Group2617 Group2617
05/06/2018 17:18:37.858 ASAAWSTEST02 Group2617 Group2617
05/06/2018 17:49:07.489 ASAAWSTEST02 Group2617 Group2617
05/06/2018 18:17:54.200 ASAAWSTEST02 Group2617 Group2617
05/06/2018 18:47:57.105 ASAAWSTEST02 Group2617 Group2617
05/06/2018 19:17:57.442 ASAAWSTEST02 Group2617 Group2617
05/06/2018 19:38:50.251 ASAAWSTEST02 GROUP2614 Group2617
05/06/2018 19:47:33.710 ASAAWSTEST02 GROUP2614 GROUP2614

Re: How to compare field values in time?

niketn — Sun, 13 May 2018 09:04:25 GMT

@batsonpm, while using streamstats to get previous value of data sorted by _time you should also be using parameter window=1. Which seems to be missing in your sample query.

It would be helpful for us to assist if you can provide tablular sample data of raw events that you are working with, along with your requirement.

Based on information so far seems like, you have time_of_change configurationItem.tags.Name and configurationItem.tags.OnPremPatchGroup in your raw events. For each configurationItem.tags.Name when the configurationItem.tags.OnPremPatchGroup of current row does not match with previous configurationItem.tags.OnPremPatchGroup, you need to fetch the previous time_of_change.

Please confirm. Also provide sample raw events in tabular format for us to assist.

Re: How to compare field values in time?

cpetterborg — Sun, 13 May 2018 17:13:25 GMT

Without a lot of information to go on, I'm going to make a stab at answering this question.

Assuming that you are getting two different (by time) result sets from a DB query with only a few values that you want to check, like you have the count of somethings that come from the db query into Splunk something like (in a CSV format in this case):

wallets,10
purses,15
backpacks,6

Then you might get another result set from the data that has the number of purses change, but no the wallets and backpacks:

wallets,10
purses,14
backpacks,6

So you then want to alert on the number of purses changing. This could be done in the following manner (I'm using a "runAnywhere" example, where the first 6 lines are just setting up the data😞

| makeresults 
| eval data="wallets,10|purses,15|backpacks,6|wallets,10|purses,14|backpacks,6"
| makemv delim="|" data
| mvexpand data
| rex field=data "(?<dbfield>.*),(?<val>.*)"
| fields - data, _time
| mvcombine dbfield
| eval cnt=mvcount(dbfield)
| search cnt=1
| mvcombine val

Instead of using the term tag, which you used in your question, I'm using the term dbfield as I would understand it from what you have asked. If you wish to understand the parts of the search above, you can just start with using the first line in a search and then add additional lines (in the order they are provided), and you will see the results at each step. Here is my explanation of the parts that make up the heart of my solution:

The mvcombine command starts the actual work of combining the data by the dbfield. The next eval command counts the number of values ( val ) for the dbfield entries. This will put multiple dbfields where they are the same for a given val. The search will get rid of the entries where the val was the same in both places. The mvcombine command will then put the dbfield back together by the different val fields. This would also get rid of entries where there was not a second entry for a given dbfield, so you may need to look at a different solution for that. This is just looking at a dbfield value changing. You can alert if there are any results returned.

In your base search you will probably want to search through just the last two sets of data that have come in.

There are other ways of doing this, but this might help get you going in the right direction. If this doesn't match your expected results, please provide a better description of what you need to accomplish.

Re: How to compare field values in time?

batsonpm — Tue, 29 Sep 2020 19:33:10 GMT

I was working with someone and this is what worked for us. I was missing the part where I needed to use spath for the fields, and the window.

| spath output=PG path=configurationItem.tags.OnPremPatchGroup
| spath output=NAME path=configurationItem.tags.Name
| streamstats global=f current=t window=2 latest(PG) as current_PG, earliest(PG) as last_PG by NAME
| where current_PG!=last_PG
| table _time, NAME, last_PG, current_PG
| rename NAME as Server, last_PG as "Old Patch Group", current_PG as "New Patch Group"
| sort +_time