How to create timechart with 4 count values?

[Dim_No]

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

[gcusello]

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

[bowesmana]

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"

[Dim_No]

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

[bowesmana]

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

[Dim_No]

( 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

[bowesmana]

---

@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! :😎

[bowesmana]

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"
...

[Dim_No]

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

531

[bowesmana]

@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

[Dim_No]

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 

[Dim_No]

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

[Dim_No]

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"

[bowesmana]

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().

[gcusello]

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

[Dim_No]

Yes, i tried.

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

Thanks 😄 

[gcusello]

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