Comparing fields with previous events

[sudhir_gandhe]

A logon script generates an event every time a user logs into the desktop. Here are the sample events in Splunk from those events -

user_A;05/10/13 10:15:01 AM;field1="cat";field2="mouse"
user_B;05/10/13 09:01:01 AM;field1="cat";field2="mouse"
user_A;05/09/13 09:05:01 AM;field1="mouse";field2="horse"
user_B;05/09/13 09:01:01 AM;field1="cat";field2="mouse"
user_A;05/08/13 11:05:01 AM;field1="mouse";field2="horse"

I want to be able to generate a report when "field1" changes per user, even compared to the last event. In this case I want a report that lists the event "user_A;05/10/13 10:15:01 AM;field1="cat";field2="mouse". Any help would be appreciated.

Thanks.

[HeinzWaescher]

Hi,

I think i have got a similiar problem, which can hopefully be solved with this kind of search.

I want to achieve a timechart, where the count per day is about all unique users who have been active on that day and the day before.

For Instance:

02.01.2013 - 2500 -> this means, that 2500 users have been active on 01.01.2013 and 02.01.013

I'm not 100 % sure about the effects of the streamstats command, but after reading the posts above, my approach would be:

sourcetype=A |bucket _time span=1d| dedup _time,user| sort _time
| streamstats current=f window=1 global=f last(_time) as previous_time by user | eval returning_user=_time-previous_time | where returning_user="86400"| timechart span=1d dc(user)

Is this a correct adjustment to achieve my needed resultt with this kind of search?

Best Regards

Heinz

[martin_mueller · Accepted solution]

You could use streamstats to copy the previous field value into the current event by user, and then do the comparisons and filters you like.

[sudhir_gandhe]

Perfect. Thank you very much.

[martin_mueller]

SQL is an entirely different thing 😛

Using streamstats, you can start like this (untested, don't have splunk for android...):

you base search | streamstats current=f window=1 global=f last(field1) as last_field1 by user | where field1!=last_field1

The streamstats copies the last value into the current event, and the where only keeps those where the value has changed. For reference, take a look at http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Streamstats in case I mixed up some switch... 🙂

[sudhir_gandhe]

I am not really a SQL guys and havent used streamstats before. Can you help build me this query? Thanks for any help.