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
Engager

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?

Labels (4)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Engager

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?

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...