topic Re: Timechart with 4 count values in Splunk Search

How to create timechart with 4 count values?

Dim_No — Tue, 20 Sep 2022 14:39:20 GMT

Hi,

I'm new as Splunk user,
I'm asking your help 🙂

I would like to create an easy dashboard with VPN datas.

My search :

index="fw_paloalto" ( sourcetype="pan:globalprotect" log_subtype="connected") OR (sourcetype="pan:system" log_subtype=auth signature="auth-fail")

With that datas, i would like to get values in a global timechart 1d like that :

>dc(user) WHERE log_subtype =connected + host="PA-3020*"

> dc(user) WHERE log_subtype =connected + host="PA-820*"

> c(user) WHERE signature="auth-fail" + host="PA-3020*"

> c(user) WHERE signature="auth-fail" + host="PA-820*"

For the moment, i'm not able to display that values in the same chart, i'm forced to have 1 chart per host.

Hope it is clear enough,

Thanks a lot for your help,

Dimitri

Re: Timechart with 4 count values

gcusello — Tue, 20 Sep 2022 08:26:45 GMT

Hi @Dim_No,

you should try using the eval in the options of the stats command, something like this:

index="fw_paloalto" ( sourcetype="pan:globalprotect" log_subtype="connected") OR (sourcetype="pan:system" log_subtype=auth signature="auth-fail") | bin span=1d -time | stats dc(eval(if(log_subtype="connected" AND host="PA-3020*",user,""))) AS "Connected to PA-3020" dc(eval(if(log_subtype="connected" AND host="PA-820*",user,""))) AS "Connected to PA-820" dc(eval(if(log_subtype="auth-fail" AND host="PA-3020*",user,""))) AS "auth-fail to PA-3020" dc(eval(if(log_subtype="auth-fail" AND host="PA-820*",user,""))) AS "auth-fail to PA-820" BY _time

I don't know what's the span of your timechart, I used 1d but you can change.

Ciao.

Giuseppe

Re: Timechart with 4 count values

bowesmana — Tue, 20 Sep 2022 23:53:33 GMT

Minor tweaks, as dc will count "" as a distinct value, so use null() instead. There seems to be no reason not to use timechart and the second two were 'counts' not dc.

index="fw_paloalto" ( sourcetype="pan:globalprotect" log_subtype="connected") OR (sourcetype="pan:system" log_subtype=auth signature="auth-fail") | timechart span=1d dc(eval(if(log_subtype="connected" AND host="PA-3020*",user,null()))) AS "Connected to PA-3020" dc(eval(if(log_subtype="connected" AND host="PA-820*",user,null()))) AS "Connected to PA-820" c(eval(if(log_subtype="auth-fail" AND host="PA-3020*",user,null()))) AS "auth-fail to PA-3020" c(eval(if(log_subtype="auth-fail" AND host="PA-820*",user,null()))) AS "auth-fail to PA-820"

Re: Timechart with 4 count values

Dim_No — Thu, 22 Sep 2022 07:01:41 GMT

Hello,
Thanks to you 2 for your answers, that helps me a lot in term of syntax !

When i do that research ( the first line of stats) :

index="fw_paloalto" | stats dc(eval(if(sourcetype="pan:globalprotect" AND log_subtype="connected" AND host="PA-3020*",user,null()))) AS "Connected to PA-3020"

That displays good events with name of users.
But in the statistics tab, that sorts me 0.

Do you have any idea ?
I don't felle rly comfortable with IF for the moment.

Thanks a lot by advance,
Have a good day.
Dimitri

Re: Timechart with 4 count values

gcusello — Thu, 22 Sep 2022 07:05:38 GMT

Hi @Dim_No,

did you tried with a space instead of null()?

index="fw_paloalto" | stats dc(eval(if(sourcetype="pan:globalprotect" AND log_subtype="connected" AND host="PA-3020*",user,""))) AS "Connected to PA-3020"

Ciao.

Giuseppe

Re: Timechart with 4 count values

Dim_No — Thu, 22 Sep 2022 07:10:29 GMT

Yes, i tried.

In statistics tab :
With space, that sorts me 1 as result.
With null(), that sorts me 0 as result

Thanks 😄

Re: Timechart with 4 count values

bowesmana — Thu, 22 Sep 2022 07:12:06 GMT

You can't do wildcard searches like this

host="PA-3020*"

as eval statements do not work that way - it's different to a search, you will need to use

match(host, "(?i)PA-3020")

Which will do a case insensitive match for host/text

Re: Timechart with 4 count values

gcusello — Thu, 22 Sep 2022 07:21:33 GMT

Hi @Dim_No,

the observation of @bowesmana is correct , you have to use match:

index="fw_paloalto" | stats dc(eval(if(sourcetype="pan:globalprotect" AND log_subtype="connected" AND match(host,"(?i)PA-3020"), user, ""))) AS "Connected to PA-3020"

Ciao.

Giuseppe

Re: Timechart with 4 count values

Dim_No — Thu, 22 Sep 2022 07:32:43 GMT

( index="fw_paloalto" AND log_subtype="connected") OR (sourcetype="pan:system" AND log_subtype="auth" AND signature="auth-fail") | stats c(eval(if(log_subtype="auth-fail" AND match(host, "(?i)PA-3020"),user,null()))) AS "auth-fail to PA-3020" c(eval(if(log_subtype="auth-fail" AND match(host, "(?i)PA-820"),user,null()))) AS "auth-fail to PA-820"

Thats works perfectly for the both dc()
But for the two count, that sorts 0 as result.
When i check in events, i well see the events with auth-fail and users.

Thanks a lot, i will soon stop to disturb you

Re: Timechart with 4 count values

Dim_No — Thu, 22 Sep 2022 07:36:44 GMT

I tried with "" instead of null().
Now i have the same result for both. The number of events seems OK but the counts doesn't arrive to separate them by PA-3020 or PA-820

( index="fw_paloalto" AND log_subtype="connected") OR (sourcetype="pan:system" AND log_subtype="auth" AND signature="auth-fail") | stats c(eval(if(log_subtype="auth-fail" AND match(host, "(?i)PA-3020"),host,""))) AS "auth-fail to PA-3020" c(eval(if(log_subtype="auth-fail" AND match(host, "(?i)PA-820"),host,""))) AS "auth-fail to PA-820"

Re: Timechart with 4 count values

bowesmana — Thu, 22 Sep 2022 07:41:08 GMT

Should the test be signature="auth-fail" not log_subtype

.. c(eval(if(signature="auth-fail" AND match(host, "(?i)PA-3020"),user,null()))) AS "auth-fail to PA-3020" ...

Re: Timechart with 4 count values

bowesmana — Thu, 22 Sep 2022 07:42:42 GMT

If you use "" as the 'false' part of these statements, then it will count it, so if it should NOT be counted, then use null().

Re: Timechart with 4 count values

bowesmana — Thu, 22 Sep 2022 07:44:41 GMT

@Dim_No wrote:

Thanks a lot, i will soon stop to disturb you

We are here to be disturbed and to get you a solution and help you learn - so keep asking! :😎

Re: Timechart with 4 count values

Dim_No — Thu, 22 Sep 2022 08:08:43 GMT

Same thing,

index="fw_paloalto" ((sourcetype="pan:globalprotect" AND log_subtype="connected") OR (sourcetype="pan:system" AND log_subtype="auth" AND signature="auth-fail")) | stats dc(eval(if(sourcetype="pan:globalprotect" AND log_subtype="connected" AND match(host, "(?i)PA-3020"),user,null()))) AS "Connected to PA-3020" dc(eval(if(sourcetype="pan:globalprotect" AND log_subtype="connected" AND match(host, "(?i)PA-820"),user,null()))) AS "Connected to PA-820" c(eval(if(sourcetype="pan:system" AND log_subtype="auth" AND signature="auth-fail" AND match(host, "(?i)PA-3020"),host,""))) AS "auth-fail to PA-3020" c(eval(if(sourcetype="pan:system" AND log_subtype="auth" AND signature="auth-fail" AND match(host, "(?i)PA-820"),host,""))) AS "auth-fail to PA-820"

Actually, the result is like that : (time = yesterday)

Connected to PA-3020 / Connected to PA-820 / auth-fail to PA-3020 / auth-fail to PA-820

221 => OK

32 => OK

531

Re: Timechart with 4 count values

Dim_No — Thu, 22 Sep 2022 09:11:39 GMT

I just tried to visualize datas on my timechart (for the both dc(eval()) which works) and i only have 2 points on midnight yesterday with the number of connection.

Ideally, i would like to have a timechart by week with the number of connection and auth-failed per day like this graph :

I thank you a lot for your help

Re: Timechart with 4 count values

Dim_No — Thu, 22 Sep 2022 09:44:39 GMT

The problem of my stats seems to be on the index with the OR :

index="fw_paloalto" ((sourcetype="pan:globalprotect" AND log_subtype="connected") OR (sourcetype="pan:system" AND log_subtype="auth" AND signature="auth-fail"))

Without it, i have good results.

I'm not sure i can navigate between 2 sourcetypes as I do. Or i do it wrong

Re: Timechart with 4 count values

bowesmana — Sat, 24 Sep 2022 03:08:06 GMT

@Dim_No

as mentioned elsewhere, please remove the "" in your if() statement - that will ALWAYS count both conditions for the IF - you MUST use null() in that case.

There are two ways to "count" with conditions in stats

| stats count(eval(if(condition=true, X, null()))) OR | stats sum(eval(if(condition=true, 1, 0)))

Either using count, where the false condition is null() or sum where the false condition is 0 and the true condition is 1