How to find routers that are not reporting a specific event?

[christopheryu]

I have the search below that shows the routers and how many times each has logged an RPD_MPLS_LSP_DOWN event.

index=XYZ sourcetype=JUNIPER RPD_MPLS_LSP_DOWN | stats count by ROUTER

My question is how do I list the routers that do not have any RPD_MPLS_LSP_DOWN event log?

RPD_MPLS_LSP_DOWN is one of the values for EVENT_TYPE field.

Thank you in advance.

[gcusello]

You have to create a lookup table called routers.csv and populate it with all the routers you have to monitor and after run a search like this (in the routers.csv file there at least one column called host).
| inputlookup routers.csv | eval count=0 | append [ search index=_internal | stats count by host] | stats sum(count) as Total | rangemap field=Total severe=0-0 low=1-1000000000 default=severe
in this way if range=severe (Total =0) Router wasn't connected in the defined period, if range=low (Total >0) Router was'n connected.

in addition you could show the search result in a graphic dashboard with icons in addition you have to copy in $SPLUNK_HOME/etc/apps/yourapp/appserver/static the following files that you can find in the Dashboard Examples - Table Icon Set (Rangemap) (App that you can find in apps.splunk.com):
- table_icons_rangemap.js
- table_decorations.css
(remember that you have to restart Splunk after copy)
and modify the first row of your dashboard as this:

 <form script="table_icons_rangemap.js, stylesheet="table_decorations.css">

About the graphic dashboard, see in Dashboard Examples the way to proceed.
(see https://answers.splunk.com/answers/454346/splunk-dashboard-widget-to-display-the-state-of-se.html#an...)

Bye.
Giuseppe

[christopheryu]

Thank you for explaining the lookup in detail, this worked for my question.

[MuS · Accepted solution]

Hi christopheryu,

I would get a list of all possible routers and use it in a lookup called routers and run the search against the lookup:

| inputlookup routers | search NOT [ search index=XYZ sourcetype=JUNIPER RPD_MPLS_LSP_DOWN | dedup ROUTER | fields ROUTER ] | ...

Just be warned that this is sub search and therefore you will have some limits. Read more about this topic here: http://docs.splunk.com/Documentation/Splunk/6.5.0/Search/Aboutsubsearches#Subsearch_performance

Another approach would be to use the timewrap command available with this App https://splunkbase.splunk.com/app/1645/ and compare the number of routers over two different time ranges and get back the missing ones.

Hope this helps ...

cheers, MuS

[christopheryu]

I was actually doing it on excel prior to posting here but eventually had the lookup in splunk, thank you!

[inventsekar]

Maybe, try this...

index=XYZ sourcetype=JUNIPER EVENT_TYPE!=RPD_MPLS_LSP_DOWN | stats count by ROUTER

[christopheryu]

That won't work as it will list all the routers since all are logging other EVENT_TYPE that are not RPD_MPLS_LSP_DOWN.

[inventsekar]

sorry, pls correct me if it's wrong..

/// it will list all the routers since all are logging other EVENT_TYPE that are not RPD_MPLS_LSP_DOWN.///

but, I think, that is what needed, right.

[twinspop]

it kinda depends on how you intend to get a full list of potential routers. I'll make the assumption that all routers are logging events, just maybe not all with the RPD_MPLS_LSP_DOWN value for EVENT_TYPE.

index=XYZ sourcetype=JUNIPER | stats count by ROUTER EVENT_TYPE | where EVENT_TYPE="RPD_MPLS_LSP_DOWN" and count=0

[christopheryu]

Thanks for the response. Yes, all routers are logging events except not all logs RPD_MPLS_LSP_DOWN. I am running your suggested search as I am typing this and it is taking so long considering that that there are 400M plus EVENT_TYPE events that it is searching. Is there any other faster way of doing it?

[twinspop]

Without knowing anything about your data, it's hard to tune. I would suggest adding terms to the first part of the search that are inclusive of all your target logs. Perhaps just "sourcetype=juniper" is too broad.