Splunk Search

How to create timechart with 4 count values?

Dim_No
Loves-to-Learn Everything

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

Labels (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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"

 

0 Karma

Dim_No
Loves-to-Learn Everything

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
Loves-to-Learn Everything
( 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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

Dim_No
Loves-to-Learn Everything

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 => OK32 => OK531531
0 Karma

bowesmana
SplunkTrust
SplunkTrust

@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

 

0 Karma

Dim_No
Loves-to-Learn Everything

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 

0 Karma

Dim_No
Loves-to-Learn Everything

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 :


graph.png
I thank you a lot for your help

0 Karma

Dim_No
Loves-to-Learn Everything

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"
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Dim_No
Loves-to-Learn Everything

Yes, i tried.

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


Thanks 😄 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...