Splunk Search

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

heybails88
Path Finder

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.

Tags (2)
0 Karma
1 Solution

heybails88
Path Finder

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

View solution in original post

0 Karma

heybails88
Path Finder

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

View solution in original post

0 Karma

mayurr98
Super Champion

hey

see image
alt text

mayurr98
Super Champion

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
Path Finder

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.

0 Karma

mayurr98
Super Champion

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!

0 Karma

heybails88
Path Finder

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.

0 Karma

mayurr98
Super Champion

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 !

0 Karma

heybails88
Path Finder

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

Thanks

0 Karma

somesoni2
Revered Legend

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
Path Finder

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

0 Karma

heybails88
Path Finder

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

0 Karma

heybails88
Path Finder

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.

0 Karma

heybails88
Path Finder

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.

0 Karma

heybails88
Path Finder

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

0 Karma

mayurr98
Super Champion

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.

0 Karma

heybails88
Path Finder

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.

0 Karma

mayurr98
Super Champion

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.

0 Karma

heybails88
Path Finder

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

0 Karma

mayurr98
Super Champion

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

0 Karma

mayurr98
Super Champion

That is great then try and let us know !

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!