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
Esteemed Legend

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
Esteemed Legend

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
Esteemed Legend

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!

Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...

Tech Talk | One Log to Rule Them All

One log to rule them all: how you can centralize your troubleshooting with Splunk logs We know how important ...

Splunk Security Content for Threat Detection & Response, Q1 Roundup

Join Principal Threat Researcher, Michael Haag, as he walks through:An introduction to the Splunk Threat ...