Splunk Search

How do I find the unique account across two different results within the same field

casey18cc
Explorer

What I am trying to accomplish is to produce a report that will demonstrate the number of unique VPN users in our environment, organized per appliance. The search looks like this

index=vpn index=vpn sourcetype=juniper_sa_log msg="Session started for user with"
| stats dc(user) as "Users" by host | sort -num(Users)

I was able to create the initial search easily; however I realized that because our VPNs appliances are clustered, each node sends logs separately, and in my search I sometimes end up having both nodes report the same users in the event a node failed over to the other.

What I would like to do then is create one search that will provide the number of unique users per VPN cluster, by combining the logs for each cluster before reporting the distinct count. (i.e. return the distinct count of users where host=a +host=b)

I have been researching for a few hours and coming up blank on this one. Any help would be appreciated.

Tags (1)
0 Karma

woodcock
Esteemed Legend

iLike this:

index=vpn index=vpn sourcetype=juniper_sa_log msg="Session started for user with" | stats dc(user) BY host | append [search
index=vpn index=vpn sourcetype=juniper_sa_log msg="Session started for user with" | stats dc(user) AS AllHosts]
0 Karma

casey18cc
Explorer

Not sure if I am doing something wrong but I get an error "unknown search command index" when using this. (I even removed the extra "index=vpn" from the beginning of the search.

0 Karma

woodcock
Esteemed Legend

I forgot the word search the first time; I edited my answer and fixed it. Try it again.

0 Karma

jkat54
SplunkTrust
SplunkTrust

You should put search before index=vpn
In the append command. Should work.

0 Karma

jkat54
SplunkTrust
SplunkTrust

Try this please:

 index=vpn sourcetype=juniper_sa_log msg="Session started for user with" | eval clustername=case(host==host1,"clusterOne",host==host2,"clusterOne",host==host3,"clusterTwo")|stats dc(user) by clustername

casey18cc
Explorer

I haven't built out the full search, but this seems to work. I am going to spend some time this week putting it all together and will let you know how it turns out

Thanks!!

0 Karma

jkat54
SplunkTrust
SplunkTrust

When you get your final eval with all the cases... You can create a macro with it to make the search smaller and so you can reuse it a lot and make changes to the macro instead of a bunch of searches with it "hard coded".

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Another alternative would be to use tags for the host.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Is there a way to identify and group appliances/host in a cluster OR all entries in your search comes from one cluster (with multiple host) only?

0 Karma

casey18cc
Explorer

I know which hosts are a part of which VPN cluster (10 clusters, 2 appliances apiece) and can craft individual searches to find the count of unique users for a given cluster.

So for example:
index=vpn index=vpn sourcetype=juniper_sa_log msg="Session started for user with" host=host01 OR host=host02| stats dc(user)

This search returns the count of distinct count of users for the cluster consisting of host01 and host02. What I would like to do is chain these together, so I have one search with the unique count of each VPN cluster that I can turn into a table or graph.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...