topic How to find users who have done an event A, but not done an event B display as timechart span of 1 month? in Splunk Search

How to find users who have done an event A, but not done an event B display as timechart span of 1 month?

asdinesh — Fri, 20 May 2022 15:21:31 GMT

I want to convert the result from https://community.splunk.com/t5/Splunk-Search/Find-users-who-have-done-an-event-A-but-not-done-an-event-B/m-p/110560 into monthly timechart.

I have tried the following query, but does not work:

index="x" (event="A" OR event="B") | stats count(eval(event="A")) as ACount count(eval(event="B")) as Bcount by userId | where ACount >= 1 AND BCount < 1 | timechart span=1mon count as result

Can anyone help me with this query?

Re: Find users who have done an event A, but not done an event B display as timechart span of 1 month

gcusello — Mon, 16 May 2022 08:10:01 GMT

Hi @asdinesh,

after a stats command you have only the fields that you used in the stats, so you don't have _time so you cannot use timechart after the stats command.

To do this, you have to add the _time field to the stats command, taking e.g. the latest value, something like this:

index="x" (event="A" OR event="B") | stats count(eval(event="A")) AS ACount count(eval(event="B")) AS Bcount latest(_time) AS _time BY userId | where ACount >= 1 AND BCount < 1 | timechart span=1mon count AS result

Ciao.

Giuseppe

Re: Find users who have done an event A, but not done an event B display as timechart span of 1 month

asdinesh — Fri, 20 May 2022 09:54:32 GMT

Hi @gcusello

Thanks for the suggestion, but it does not return any time chart, it shows "No results found". I don't think I can use the latest(_time) here as it might break the monthly time chart. I need to first assort the events into monthly buckets or something. Any suggestions on that?

Re: Find users who have done an event A, but not done an event B display as timechart span of 1 month

gcusello — Fri, 20 May 2022 09:59:21 GMT

Hi @asdinesh,

to use earliest or latest is a your decision that doens't move the approach.

If you haven't any result check the conditions, you can debug all searches deleting, one by one, rows from the end and understanding which is the one that block results.

Ciao.

Giuseppe