match fields in different events and send a alert

[SS1]

Hello Everyone,

I have two searches

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

search2=> index="appv" sourcetype=sql_appv_packageversion

Search 1 events look like below,

2020-11-13 01:14:12 IP PUT /packages/version/323 - 8080 **** *** 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 events look like below,

2020-11-12 17:58:00.062, Id="323", PackageGuid="*", Name="ApacheMaven-363", VersionGuid="*", VersionNumber="0.0.0.1", Size="0", PackageUrl="\*", UserPolicyFromManifest="<UserConfiguration xmlns:xsd="*" xmlns:xsi="h*" PackageId="*" DisplayName="(App-V Default)" xmlns="*">

now i need to match "323" in both the events and send out an alert. Is that possible? also, there is a timestamp difference between both events

[SS1]

thanks @gcusello . This one almost worked but not displaying the users ins user column

I tried both the below searches

index="appv" (sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_packageversion) | rex "\/packages\/\w+\/(?<Id>\w+)" | stats dc(sourcetype) AS dc_sourcetype values(Name) AS Name values(User) AS user BY Id | where dc_sourcetype=2

index="appv" (sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_packageversion) | rex "\/packages\/\w+\/(?<Id>\w+)\s+-\s+\d+\s+(?<user>[^ ]+)" | stats dc(sourcetype) AS dc_sourcetype values(Name) AS Name values(User) AS user BY Id | where dc_sourcetype=2

Results of the search,

Id          dc_sourcetype                  Name                                          user
323              2                                 Putty v0.72
430               2                                 XMLSpyProf2020-V3

[gcusello]

Hi @SS1,

the problem is tha in the second search you didn't used the same field name in regex extraction and stats (User and user): fields are case sensitive!

index="appv" (sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_packageversion) 
| rex "\/packages\/\w+\/(?<Id>\w+)\s+-\s+\d+\s+(?<User>[^ ]+)" 
| stats dc(sourcetype) AS dc_sourcetype values(Name) AS Name values(User) AS User BY Id 
| where dc_sourcetype=2

Ciao.

Giuseppe

[SS1]

Thanks @gcusello  for your help. I have used your last query and it is giving below results

Id           dc_sourcetype                  Name                              User
366          2                          FiredrillAgent_2.2.445          userB
425         2                          TeamViewer-15_9_4-V1         userA

We have 1 more requirement. we have Enabled=0 and Enabled=1 in the last line of 2nd search events, we need to check if Enabled = 0 it should mention as Unpublish and if enabled=1 it should mention as Publish (please check sample table below)

In sourcetype=sql_appv_packageversion, we have events like below. WHich has Enabled=0 or 1 in last time

2020-11-12 17:58:00.062, Id="323", PackageGuid="*", Name="ApacheMaven-363", VersionGuid="*", VersionNumber="0.0.0.1", Size="0", PackageUrl="\*", UserPolicyFromManifest="<UserConfiguration xmlns:xsd="*" xmlns:xsi="h*" PackageId="*" DisplayName="(App-V Default)" xmlns="*">
</DeploymentConfiguration>", SequencerArchitecture="x64", Enabled="0", TimeAdded="2020-11-03 13:36:46.090", TimeChanged="2020-11-04 23:00:17.193", TimeEnabled="2020-11-03 13:55:24.209"

Sample table:

Id          dc_sourcetype                   Name                                       User                                           Action
366            2                             FiredrillAgent_2.2.445              userB                                       Publish
425             2                            TeamViewer-15_9_4-V1          userA                                    Unpublish

[gcusello]

Hi @SS1,

following my approach, you can add to the stats command all the fields you need and after use eval to display Publish/Unpublish instead of 0/1:

index="appv" (sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_packageversion) 
| rex "\/packages\/\w+\/(?<Id>\w+)\s+-\s+\d+\s+(?<User>[^ ]+)" 
| stats dc(sourcetype) AS dc_sourcetype values(Name) AS Name values(User) AS User values(Enabled) AS Enabled BY Id 
| where dc_sourcetype=2
| eval Enabled=if(Enabled="0","Unpublish","Publish")

Obviously this is correct if you have Enabled in each event of the second sourcetype, otherwise. the eval must be a little different

| eval Enabled=if(Enabled="0","Unpublish",if(Enabled="1","Publish"),"")

Ciao.

Giuseppe

[SS1]

Thanks @gcusello , i have used below query

index="appv" (sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_packageversion) | rex "\/packages\/\w+\/(?<Id>\w+)\s+-\s+\d+\s+(?<User>[^ ]+)" | stats dc(sourcetype) AS dc_sourcetype values(Name) AS Name values(User) AS User values(Enabled) AS Enabled BY Id | where dc_sourcetype=2 | eval Enabled=if(Enabled=="0","Unpublish", "Publish")

It is working intermittently, events where it should show as Unpublish it is showing as publish

[SS1]

@gcusello is there w way we can filter out the events based on  Enabled=0 or Enabled=1 and create 2 seperate alerts?

[SS1]

Thanks @gcusello , this one perfectly worked.

One last thing is can we match time of the event too ?

[gcusello]

Hi @SS1,

yes, you can match for every common field, but beware because also a minimal difference (a second or a millisecond) could fail the correlation!

if you want to use the timestamp in correlation, you have to be sure that the timestamp is the same in both events types, if not you could try to group events using the bin command but you continue to have problems for events near the change minute; for all these reasons i usually avoid to use timestamps for correlations!

Ciao.

Giuseppe
P.S.: for the other people of Community, if this answer solves your need, please, accept it and Karma Points are appreciated by all the contributors 😉

[gcusello]

Hi @SS1,

you have to extract the "Id" field from the first sourcetype and then merge the two searches, something like this:

index="appv" (sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_packageversion)
| rex "\/packages\/\w+\/(?<Id>\w+)"
| stats dc(sourcetype) AS dc_sourcetype BY Id
| where dc_sourcetype=2

In this way you select only the Ids present in both the sourcetypes, obviously, if you want a different Use Case (e.g. the ones not present in one of the sourcetypes), you have to add an additional condition.

You can extract the "Id" field in the search using the rex command (like in the above example search) or (better) extratct it in a field assiciated to the "AppV-User" sourcetype.

You can test the regex to extract "Id" field at https://regex101.com/r/Itjgvy/1

Ciao.

Giuseppe

[SS1]

Thanks @gcusello  I am getting the results as below

Id            dc_sourcetype

323              2

430              2

Let me update the question a little. I would like to have stats like below by comparing the IDs from 2 searches

Id      Name     User

XX      XXX        UserX

Below are the two searches,

Search 1 events look like below,

2020-11-13 01:14:12 IP PUT /packages/version/323 - 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 events look like below,

2020-11-12 17:58:00.062, Id="323", PackageGuid="*", Name="ApacheMaven-363", VersionGuid="*", VersionNumber="0.0.0.1", Size="0", PackageUrl="\*", UserPolicyFromManifest="<UserConfiguration xmlns:xsd="*" xmlns:xsi="h*" PackageId="*" DisplayName="(App-V Default)" xmlns="*">

Values of Name, Id, user keeps changing

[to4kawa]

sample:

index=_internal | head 1 | fields _raw
| eval _raw="2020-11-13 01:14:12 IP PUT /packages/version/323 - 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"
| appendpipe [ eval _raw="2020-11-12 17:58:00.062, Id=\"323\", PackageGuid=\"*\", Name=\"ApacheMaven-363\", VersionGuid=\"*\", VersionNumber=\"0.0.0.1\", Size=\"0\", PackageUrl=\"\*\", UserPolicyFromManifest=\"<UserConfiguration xmlns:xsd=\"*\" xmlns:xsi=\"h*\" PackageId=\"*\" DisplayName=\"(App-V Default)\" xmlns=\"*\">\""]
| rex "(?<comment>(?# These are sample logs, from here, the logic))"
| rex "(GET|PUT|OPTIONS|HEAD)\s+(?<url>\S+)\s\S+\s\S+\s(?<user>\S+)"
| eval id=mvindex(split(url,"/"),-1)
| kv
| eval Id=coalesce(Id,id)
| eval Name=mvindex(split(Name,"-"),0)
| stats max(Name) as Name  max(user) as User by Id

Maybe I've overdone it.

[gcusello]

Hi @SS1,

to do this you need only to add two values options to your stats command:

index="appv" (sourcetype="AppV-User" *PUT /package*) OR (sourcetype=sql_appv_packageversion)
| rex "\/packages\/\w+\/(?<Id>\w+)"
| stats dc(sourcetype) AS dc_sourcetype values(Name) AS Name values(User) AS user BY Id
| where dc_sourcetype=2

if you haven't already extracted the User field, you have to modify the regex to extract it. 

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

that you can test at https://regex101.com/r/Itjgvy/2 

Ciao.

Giuseppe