how can I create a top 5 list of multiple values from one source

[heybails88]

I have an index from a forwarder that looks something like this:
"index=indexname DEBUG Rule="Rule One" OR "Rule Two" OR "Rule Three" etc."
I'd like to build a summary of all the Rules that come in via the log that is being read by the forwarder. Something like a top 5 summary of Rules that came out. I've tried to parse through a lookup table, but that didn't do what I wanted. Having difficulty with something that should be pretty easy.

[heybails88 · Accepted solution]

Ultimately what I needed was a regex extraction called "matched" which looked like this

matched\s(?P.*)

This added the events that came from log on the forwarder. Then create the top 5.

index=index matched=* |stats count by matched |sort - count

[mayurr98]

hey

see image

[mayurr98]

okay so I think your question is regarding there are multiple values for a field Rule .

step1 :so what you can do is, you can create mylookup.csv in which you will have column Rule and values i.e. Rule one two three, etc you need to search

step 2 :add this lookup table using this doc
https://docs.splunk.com/Documentation/Splunk/7.0.1/PivotTutorial/AddlookupfilestoSplunk

step 3 :and the write your search query as follows:

index=indexname DEBUG [inputlookup mylookup.csv] | top limit=5 Rule

which means that your search will actually run as

index=indexname DEBUG Rule="Rule One" OR "Rule Two" OR "Rule Three" etc.." | top limit=5 Rule

you can see this search in job>>inspect job>>saerch job propertise>>normalized search
Let me know if this helps!

[heybails88]

Thanks for the help. I did build a lookup table and added it, but still not seeing any results. My lookup looks like this. I thought about quotes around the :

Rule,Values
1,Questionable Radio Link
2,Bad Cable
3,Data Abuser Warning
etc.
I inspected the job, but I don't see anything glaring except it's not finding anything. But at least this tells me I was on the right track. I'll keep poking.

[mayurr98]

I am assuming in your logs you have Rule=“bad cable” and so on format right?
If it is then why does your lookup contain 2 columns...
Build a lookup that have only one column just like in splunk ...
In splunk if you have Field name as Rule and value as Bad cable

Then your lookup should have only one column i.e
Rule
Bad Cable
Questionable radio link
...
And so..

Keep column name of csv same as field name in splunk

I hope this helps you!

[heybails88]

Yeah, unfortunately, I'd already tried both of your suggestions before I even opened this question and answer. I thought you were on to something with having 2 columns. Hmmm, I know I'm close.

[mayurr98]

This method is 100 percent working . Just that you need to match fields and value properly .
Yes even i feel this is the better method try and accept the answer which helped to get the work done !

[heybails88]

I will as soon as I can get it to work.

Thanks

[somesoni2]

If your lookup has two columns and one of them (column Values) match, exactly, Rule field in your raw data, you can run like this

index=indexname DEBUG [inputlookup mylookup.csv | table Values | rename Values as Rule] | top limit=5 Rule

[heybails88]

I now, have one column called Rule. There doesn't seem to be a need for two.

[heybails88]

Either way, doesn't work. The only search that does work is with the OR statements.

[heybails88]

But building a top 5 doesn't work. So what I'm thinking is the lookup table DOES work, but the top 5 for whatever reason, doesn't.

[heybails88]

OK, wait, I need to back up. The only thing that works is

"index=aircontrol DEBUG "Questionable Radio Link" OR Rule="Bad Cable" OR Rule="Data Abuser Warning" OR Rule="Data Abuser Critical" OR Rule="AP Down" OR Rule="Low Noise Floor" OR Rule="Low Noise Floor Access Points" OR Rule="High Latency" OR Rule="Warning CPU" OR Rule="High CPU" OR Rule="Warning CPU Access Points" OR Rule="High CPU Access Points" OR Rule="Number of Client >=25" OR Rule="Number of Client >=30" OR Rule="Number of Client >=35" OR Rule="Customer Interface LAN Status = Down"

When I try "index=aircontrol DEBUG [inputlookup aircontrol.csv]" I get nothing. They're basically the same.

[heybails88]

Inspecting the job shows that the remote search seems to be doing what it should:

litsearch (index=aircontrol DEBUG (Rule="Questionable Radio Link" OR Rule="Bad Cable" OR Rule="Data Abuser Warning" OR Rule="Data Abuser Critical" OR Rule="AP Down" OR Rule="Low Noise Floor" OR Rule="Low Noise Floor Access Points" OR Rule="High Latency" OR Rule="Warning CPU" OR Rule="High CPU" OR Rule="Warning CPU Access Points" OR Rule="High CPU Access Points" OR Rule="Number of Client >=25" OR Rule="Number of Client >=30" OR Rule="Number of Client >=35" OR Rule="Customer Interface LAN Status = Down")) | fields keepcolorder=t "*" "_bkt" "_cd" "_si" "host" "index" "linecount" "source" "sourcetype" "splunk_server" | remotetl nb=300 et=1516035600.000000 lt=1516125059.000000 remove=true max_count=1000 max_prefetch=100

[mayurr98]

it is working perfectly see image..for this i had configured lookup table and look definitions as given in this doc
https://docs.splunk.com/Documentation/Splunk/7.0.1/PivotTutorial/AddlookupfilestoSplunk
also at both steps you need to change permissions to global.
see the query and method is right as I have shown you the proof .Just that you need to debug where you are going wrong . I have done exactly what you are doing in your environment.

[heybails88]

Permissions are set globally. So that's not it. I should be seeing events with the lookup, so perhaps the data is not parsed correctly.

[mayurr98]

oh then parse the data correctly as lookup and query do not have any problem. extract the correct value into Rule field and it will solve your problem.values can be caseinsensitive but it should be correct.

[heybails88]

Data is fine as I get data without the lookup. I just can't do anything with it. The lookup table is not helping. Thanks for the help

[mayurr98]

then try |stats count by Rule | sort limit=5 count desc

[mayurr98]

That is great then try and let us know !