I'm wanting to find out if it's possible to take a list of items in a text file, conduct a search against that list and report the number of times each item appears in the Splunk data.
For example, I will have list of ALL available firewall policy names in a text file. I would like to be able to use the contents of that file and compare each rule name against the firewall data in a search and show a count of the number of times each entry in the policy file appears in the search data.
Thanks in advance.
@balcv ,
Assuming you have a look up file (policy_list) with policy names (policy_name) and your search event also has the policy names as a field. Rename any one of them to have same field names in both events and look up file.
Try this and lets know if it works for your requirement.
index="your firewall index" [|inputlookup policy_list |fields policy_name] |stats count by policy_name
This will initiate a search index="your firewall index" policy_name="policy1" OR policy_name="policy2" ....OR ..
If you need only a count, the search could be rewritten as
index="your firewall index" |stats count as _null by policy_name
|search[ |inputlookup policy_list |fields policy_name] |stats count by policy_name
@balcv ,
Assuming you have a look up file (policy_list) with policy names (policy_name) and your search event also has the policy names as a field. Rename any one of them to have same field names in both events and look up file.
Try this and lets know if it works for your requirement.
index="your firewall index" [|inputlookup policy_list |fields policy_name] |stats count by policy_name
This will initiate a search index="your firewall index" policy_name="policy1" OR policy_name="policy2" ....OR ..
If you need only a count, the search could be rewritten as
index="your firewall index" |stats count as _null by policy_name
|search[ |inputlookup policy_list |fields policy_name] |stats count by policy_name
That's fantastic @renjith.nair . Thank you.
Can this work another way? List all the entries in policy_list and count where there is a match to the search data, or simply show 0 (zero) where there is no match with the searched data.
So in effect I'm wanting to know those entries in policy_list that do not get a match in the searched data.
@balcv ,
Sure, try something like
index="your firewall index" |stats count as _null by policy_name|eval flag="search"
|append [ |inputlookup policy_list |fields policy_name|eval flag="list"]
|stats count,values(flag) as flag by policy_name|eval count=count-1|where mvindex(flag,0)=="list"
count=count-1 because it adds the count from lookup also together with events and hence wanted to remove that. Flags to indicate from where the data comes from and filter it only for those coming from list. Test it and see if it works for you. We shall tweak it based on your required final output
Awesome. So can I just confirm, the output lists everything from the file which is what I was after, then the count field either shows a 1 or a 0. Was that the intention, or should the count be the total number of times each one appears in the search. Either is fine. It's the 0 (zeros) I'm most interested in.
It lists all from your lookup file. The count is total number of times each one appears. We can change that to 0 and 1also for non-matching & matching. Please test it with your dataset samples and confirm 🙂
This is what I tried with my sample data using splunk's internal lookups and it works fine. Have a look at this as well - you can run it anywhere
| makeresults |eval country="Angola,Angola,Bulgaria,Australia,None"|makemv country delim="," | mvexpand country |eval source="search"
|append [|inputlookup geo_attr_countries.csv|table country,continent|eval source="lookup"]
|stats count,values(source) as source by country|sort - count|eval found=if(mvcount(mvdedup(source)) > 1,"Yes","No")
Thanks you so much for your help. I really appreciate it.