topic Re: stats count sum in Splunk Search

stats count sum

kokanne — Thu, 15 Mar 2018 08:13:17 GMT

Why does the following query not display the number of logins and logouts

(index="ggg-sec") EventCode=4624 OR EventCode=4634 
    [| inputlookup dfggfdf.csv] 
| stats count sum(EventCode = "4624") as LogIns, sum(EventCode = "4634") as LogOuts by user 
| fields - count

Thanks

Re: stats count sum

deepashri_123 — Tue, 29 Sep 2020 18:27:41 GMT

Hey kokanne,

Try this:
(index="wineventlog-sec") EventCode=4624 OR EventCode=4634
[| inputlookup sv_externe_leveranciers.csv]
|eval Condition=case(EventCode == 4624,"Logins",EventCode == 4634,"LogOuts") |stats count by Condition,user
Let me know if this helps!!

Re: stats count sum

kokanne — Thu, 15 Mar 2018 08:23:31 GMT

Error in 'eval' command: The arguments to the 'case' function are invalid.

Re: stats count sum

deepashri_123 — Thu, 15 Mar 2018 08:27:53 GMT

I have edited, can you try now!!

Re: stats count sum

kamlesh_vaghela — Thu, 15 Mar 2018 08:31:00 GMT

Hi @kokanne,

Can you please try it by replacing stats with this?

| stats count sum(eval(EventCode = "4624")) as LogIns, sum(eval(EventCode = "4634")) as LogOuts by user

Like:

 (index="wineventlog-sec") EventCode=4624 OR EventCode=4634 
     [| inputlookup sv_externe_leveranciers.csv] 
| stats count sum(eval(EventCode = "4624")) as LogIns, sum(eval(EventCode = "4634")) as LogOuts by user

Thanks

Re: stats count sum

kokanne — Thu, 15 Mar 2018 08:31:37 GMT

It works, but I would like instead of a column called "Condition", that the column is called either log in or log out, is that possible, and then also user and the count

Re: stats count sum

kokanne — Thu, 15 Mar 2018 08:36:20 GMT

Thanks, does exactly waht i want

Re: stats count sum

kokanne — Thu, 15 Mar 2018 09:34:31 GMT

do you know how to speed it up? @kamlesh_vaghela

it takes 34 minutes to run this query for last 24 hours, i need this to run every month for a monthly report

Re: stats count sum

deepashri_123 — Thu, 15 Mar 2018 09:40:37 GMT

is there any reason for using lookup?

Re: stats count sum

kokanne — Thu, 15 Mar 2018 09:43:47 GMT

for the user names

Re: stats count sum

amitm05 — Mon, 07 May 2018 07:57:57 GMT

You need to accelerate your report.
Mind that setting the schedule and time window for your acceleration should be according to your need.
E.g. If you want this report monthly for the last month.

I think you can probably set the acceleration schedule for last midnight of the day when you want this report. And set the time window of your search also to last month.

Please upvote if this helps you 🙂