Search for Access Page Tracking

[melonman]

Hi,

I think this is similar case as user URL tracking on web sites,
and I am trying to track what page a user have visited.

Here is my log (simplified).
This includes user, previous page, current page and timestamp in unix time.

1361836690 user=u01 action=move prev=p1 current=p2
1361836690 user=u02 action=move prev=p2 current=p4
1361836691 user=u01 action=move prev=p2 current=p3
1361836691 user=u02 action=move prev=p5 current=p6
1361836691 user=u03 action=move prev=p2 current=p1
1361836692 user=u02 action=move prev=p5 current=p6
1361836693 user=u03 action=move prev=p1 current=p3
1361836696 user=u01 action=move prev=p3 current=p4
1361836698 user=u03 action=move prev=p3 current=p4
1361836699 user=u03 action=move prev=p5 current=p6
1361836708 user=u03 action=move prev=p6 current=p1

What I want to do is to list which page a user has visited by connecting prev in a event and current in the next latest event that matches to prev value in a last event.

I think SQL can query such information, but I need to do this by Splunk Language.

I would like to get some help with this from anyone who knows this type of search.

Thank you in advance,

[melonman]

I want to connect/join on prev and current values. I am trying to figure out how to use selfjoin command to a pair of keys in a separated events.

[Splunk_Shinobi · Accepted solution]

How about this?

sourcetype=atype
| eval t=_time 
| convert ctime(t) 
| eval a="(".t.") ".prev."->".current 
| eval p=prev.",".current 
| makemv delim="," p 
| transaction user p 
| table user a


user              a
---- ----------------------------
u01  (02/26/2013 08:58:10) p1->p2
     (02/26/2013 08:58:11) p2->p3
     (02/26/2013 08:58:16) p3->p4

u02  (02/26/2013 08:58:11) p5->p6
     (02/26/2013 08:58:12) p5->p6

u02  (02/26/2013 08:58:10) p2->p4

u03  (02/26/2013 08:58:19) p5->p6
     (02/26/2013 08:58:28) p6->p1

u03  (02/26/2013 08:58:11) p2->p1
     (02/26/2013 08:58:13) p1->p3
     (02/26/2013 08:58:18) p3->p4

[melonman]

Is there any other way without using transaction command? I think there is a event count limitation that transaction command can handle...?

[vincesesto]

Hey Melonman,

Just a little bit more information if you could. Are you wanting to do the self join on the user value or on the prev and current values...There is a selfjoin search function within splunk and more info can be found at:
http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/Selfjoin

Regards,

Vince

[melonman]

Rephrasing my question, is there any way to do self join by Splunk Language?