topic Re: how can I create a top 5 list of multiple values from one source in Splunk Search

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

heybails88 — Mon, 15 Jan 2018 20:15:03 GMT

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.

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

somesoni2 — Mon, 15 Jan 2018 20:33:38 GMT

You need to define the rule based on which you want to find the top 5 rules. Example, if you want to find out top 5 Rule based on number of events, you can do like this

index=indexname DEBUG Rule="Rule One" OR Rule="Rule Two" OR Rule="Rule Three"...all rules that you're interested in..
| top 5 Rule

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

mayurr98 — Tue, 16 Jan 2018 08:39:38 GMT

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!

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

heybails88 — Tue, 16 Jan 2018 15:50:32 GMT

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.

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

heybails88 — Tue, 16 Jan 2018 15:51:20 GMT

Thanks for the info. A lookup table is what I need, which I had defined, but it wasn't right. However, I still need something else.

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

mayurr98 — Tue, 16 Jan 2018 16:15:15 GMT

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!

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

heybails88 — Tue, 16 Jan 2018 16:39:40 GMT

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.

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

mayurr98 — Tue, 16 Jan 2018 16:49:25 GMT

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 !

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

heybails88 — Tue, 16 Jan 2018 16:52:04 GMT

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

Thanks

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

somesoni2 — Tue, 16 Jan 2018 17:22:30 GMT

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

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

mayurr98 — Tue, 16 Jan 2018 17:35:12 GMT

Yes that is also will do.

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

heybails88 — Tue, 16 Jan 2018 17:41:33 GMT

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

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

mayurr98 — Tue, 16 Jan 2018 17:44:35 GMT

That is great then try and let us know !

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

heybails88 — Tue, 16 Jan 2018 17:44:43 GMT

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

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

heybails88 — Tue, 16 Jan 2018 17:46:13 GMT

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.

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

mayurr98 — Tue, 16 Jan 2018 17:48:28 GMT

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

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

heybails88 — Tue, 16 Jan 2018 17:51:42 GMT

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.

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

heybails88 — Tue, 29 Sep 2020 17:40:05 GMT

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

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

mayurr98 — Tue, 16 Jan 2018 18:21:22 GMT

hey

see image

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

mayurr98 — Tue, 16 Jan 2018 18:24:09 GMT

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.