Match fields in different searches and send alert

[SS1]

Hi,

I have two searches

Search 1 = index="appv" sourcetype="AppV-User" *PUT /package*

Search 2 = index="appv_latest" sourcetype=sql_appv_latest

Search 1 results:

2021-01-12 17:28:51 IP PUT /packags/version/463 - 8080 UserX Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/86.0.4240.111+Safari/537.36 ** 200 0 0 31

Search 2 results:

2021-01-12 16:07:00.006, ID="463", PkgName="SnagIT-v2102-V1", Status="Published", TimeChanged="2021-01-13 00:06:17.1970000"

now i need to match "463" in both the events and send out an alert

Table can be something like below,

Id          PkgName                                       User                                        Status
463       SnagIT-v2102-V1                      UserX                                           Published

@gcusello 

[bowesmana]

@SS1 

You probably want the SPL from this example. This example is showing how to process the data from your example results and you want the code from the 'rex' statement.

| makeresults
| eval event=split("2021-01-12 17:28:51 IP PUT /packags/version/463 - 8080 UserX Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/86.0.4240.111+Safari/537.36 ** 200 0 0 31~2021-01-12 16:07:00.006, ID=\"463\", PkgName=\"SnagIT-v2102-V1\", Status=\"Published\", TimeChanged=\"2021-01-13 00:06:17.1970000\"","~")
| mvexpand event
| eval _raw=event
| extract 
| eval _time=strptime(_raw,"%F %T.%Q")
| rex field=_raw "/packags/version/(?<PutId>\d+) - \d+ (?<User>\w+)"
| eval Id=coalesce(PutId, ID)
| fields - PutId ID
| stats values(PkgName) as PkgName values(User) as User values(Status) as Status by Id

However, your initial search needs to be using this

(index="appv" sourcetype="AppV-User" *PUT /package*) OR
(index="appv_latest" sourcetype=sql_appv_latest)

which will result in both result types being in the Splunk pipeline. 

In the example, the 'extract' is simply converting all the X=Y fields in your second data set to fields in Splunk - but you probably will already have those fields auto-extracted anyway.

This type of search is better than using a 'join' as joins are to be avoided in Splunk where possible as they are slower than using stats and have some limitations not always obvious.

Hope this helps.

[SS1]

@bowesmana 

Hi,

I have tried that query and it is not giving expected results.

I tried to use below query, but couldn't get complete details. Table is not giving values for PkgName & Status values. Can we improve this search query?

(sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_latest) | rex "\/packages\/\w+\/(?<Id>\w+)\s+-\s+\d+\s+(?<User>[^ ]+)"| stats values(PkgName) as PkgName values(User) as User values(Status) as Status by Id

Below is the table i am getting

Id            PkgName            User         Status
366                                        userX

[bowesmana]

OK, so you need to see what fields are available for your sourcetype=sql_appv_latest - I believe that is the one that will have ID=aa, PkgName=xx and Status=yy

so if you do a simple verbose search, what fields do you have?

If you do not have either of those fields auto extracted, then you can either do the 

| extract

command, which will automatically extract your X=Y fields from that data or use a rex command 

| rex field=_raw "ID=.(?<ID>\d+).*PkgName=.(?<PkgName>[^\"]*).*Status=.(?<Status>[^\"]*)"
| eval Id=coalesce(Id, ID)

Note that in the above, your ID field from the second data set needs to be 'joined' with the Id from your first data set, hence the coalesce command, which will assign the first non null value of the arguments to the Id field.

[SS1]

Hi,

If i do a simple verbose search as below, 

(sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_latest)

fields on the left are below (attached), I extracted the 'user' field

I ran the search as you suggested, but the user field is still empty

(sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_latest)
| rex field=_raw "ID=.(?<ID>\d+).*PkgName=.(?<PkgName>[^\"]*).*Status=.(?<Status>[^\"]*)"
| eval Id=coalesce(Id, ID)
| stats values(PkgName) as PkgName values(user) as user values(Status) as Status by Id

Id                  PkgName                user        Status
206        WinSCP-v5.13.5                       Published

[bowesmana]

The problem is that the full search is needed to get all the ducks in a row

(1) data from the PUT /packages... data source needs to have the Id field extracted

(2) data from the sql_appv_latest sourcetype needs the ID, PkgName and Status fields

- the Id from (1) and the ID from (2) need to be the same field, hence the coalesce() will make Id be either Id (from 1) or ID (from 2)

In your latest example, where 'user' is missing, you need your original rex statement, so that Id and user are extracted from data type 1.

| rex "\/packages\/\w+\/(?<Id>\w+)\s+-\s+\d+\s+(?<User>[^ ]+)"

Note that fields are case sensitive, so user and User, Id and ID are different fields.

Based on your image of the fields shown in verbose mode, you already have ID, PkgName and Status, so I would not expect you to require the rex statement that extracts those fields.

Generally when diagnosing a search, find the simplest time window you can use that gives data and then gradually build up the line to see what results you're getting with each additional line of SPL, but from what you're getting now, it looks like you should be able to get the result you're after.

[SS1]

Thanks @bowesmana 

I am using below search,

(sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_latest)
| rex "\/packages\/\w+\/(?<Id>\w+)\s+-\s+\d+\s+(?<User>[^ ]+)"
| eval Id=coalesce(Id, ID)
| stats dc(sourcetype) AS dc_sourcetype values(PkgName) as PkgName values(user) as user values(Status) as Status by Id
| where dc_sourcetype=2

and alert action to trigger a email alert per result but not getting the alert when there is event in the table. any idea?