How do I write a search to correlate events between two sourcetypes to see if a user logged in to a certain server during their VPN session?

[akdake]

HI,

I want to correlate two sourcetypes.

The first sourcetype is VPN logged event. For example, userA logged event as follows:

2015-10-18 18:06:45  1.1.1.1  userA   logged in , connected  to network....
2015-10-18 19:06:45  1.1.1.1  userA   logged out , disconcerted from network.....

IF userA logged in to the specialized Windows server by VPN channel during their VPN session, Windows log is as follows,

2015-10-18 18:25:45  account=userA   eventid=477x.  ....

I want to search whether userA logged in or not to the specialized Windows server during their VPN session. How do I design the correlation search? TKS.

[woodcock]

Like this:

sourcetype=inout OR sourcetype=event | reverse | eval type=case(searchmatch("connected to network"), "connect", searchmatch("disconnected from network"), "disconnect", true(), "event") | streamstats count (eval(type="connect")) AS sessionID by user | eventstats latest(type) AS lastType by user sessionID

To see ones that were logged-in, just add this:

| where type="event" AND lastType!="event"

To see ones that were not logged-in, add this instead:

| where type="event" AND lastType="event"

[akdake]

HI

It seems still doesn't work yet. The the VPN log looks like this:

 2015-10-18 18:06:45  wan_ip=1.1.1.1  user=userA   stats="logged in", info=connected  to network....
 2015-10-18 19:06:45  wan_ip=1.1.1.1  user=userA   stats="logged out", info=disconcerted from network.
 2015-10-19 19:06:45  wan_ip=2.2.2.2  user=userB   stats="logged in", info=connected  to network....
 2015-10-19 20:06:45  wan_ip=2.2.2.2  user=userB   stats="logged out", info=disconcerted from network.
 2015-10-20 14:06:45  wan_ip=3.3.3.3  user=userC   stats="logged in", info=connected  to network....
 2015-10-20 21:06:45  wan_ip=3.3.3.3  user=userC   stats="logged out", info=disconcerted from network.

And the win log just like this:

2015-10-18 18:25:45  account=userA   eventid=477x key=aa ...
2015-10-18 22:25:45  account=userA   eventid=477x. key=aaa ...
2015-10-19 10:35:45  account=userB   eventid=477x.  key=bb
2015-10-19 15:35:45  account=userC   eventid=477x.  key=CC

I carried out the search according to your advice,

index=aabb |reverse | eval type=case(searchmatch("logged in"),"connect", searchmatch("logged out"),"disconnect", searchmatch("eventid"),"event")| streamstats  count(eval(type="connect")) as sessionID by user| eventstats latest(type) as lastType by user sessionID | where type="event" AND lastType="event"

Still cannot get the result. The win log event "...key=aa..." and "...key=cc.." should be found

Pls advise me more ,TKS

[akdake]

TKS for helping, Howerver It doesn't work, return "Unbalanced quotes" , and :Error in 'eval' command, The arguments to the 'case' function are invalid"

Pls. give me more advices, Many thanks.

[woodcock]

I had a couple of tiny mistakes and have corrected my original answer; see if it works now.