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

[asdinesh]

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-ev... 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?

[gcusello]

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

[asdinesh]

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?

[gcusello]

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