topic Re: Analysis using lookup file in Splunk Search

Analysis using lookup file

Deepz2612 — Wed, 09 Dec 2020 05:51:25 GMT

Hi,

I have a lookup file with the entire list of service names,now i want to perform a search to have the count of the service and and for the service not present in logs for the selected time range but present in lookup file,the count has to be shown as 0

Please assist @niketn

Re: Analysis using lookup file

renjith_nair — Thu, 10 Dec 2020 03:41:30 GMT

Try

"your base search" |stats count by service |inputlookup yourlookup.csv append=true |fillnull count value=0 |stats sum(count) as count by service

Re: Analysis using lookup file

Deepz2612 — Wed, 09 Dec 2020 07:22:20 GMT

@renjith_nair Thanks for the suggestion,this worked,but i have another question,

When the service is present in both logs and lookup file,it should take the function (field that is extracted using regex) from logs..

Re: Analysis using lookup file

renjith_nair — Wed, 09 Dec 2020 08:48:08 GMT

Glad that the solution worked. 👍 would be appreciated 🙂

Would you mind sharing the search and explain what you currently have and what do you expect? Is that function/field is after the stats function?

Re: Analysis using lookup file

Deepz2612 — Wed, 09 Dec 2020 12:01:30 GMT

@renjith_nair

The event in which service name is present in the same event function name corresponding to service name is also present.

Now i have a lookup file with whole list of service names

My search has to look for service name in the log,if present it bring its corresponding function name and also the count by service and function name..

And for services not present in log but present in lookup file,it should bring the count as zero

This is my requirement

Re: Analysis using lookup file

renjith_nair — Thu, 10 Dec 2020 03:57:31 GMT

Try this run anywhere example and check if it works for your use case

we have few events with country & continent and we compare it against the lookup which has only a list of countries.

Re: Analysis using lookup file

Deepz2612 — Tue, 22 Dec 2020 09:44:27 GMT

@renjith_nair @I'm sorry,this is not working for me.
Let me explain it once again as I'm not sure if i had explained it right earlier.
For say,below are the services,and few of them are in the lookup file.
Services
Service0- present in lookup
Service45
Service05
Service078 - present in lookup
Now,1.I should find the count of all the services whether present in lookup file or not.
i.e.,a. service is present in log but not in lookup file
b.service presnt in both log and lookup file
c.service not present in log but in lookup file (in this case the count will be zero).
2.And for the service,the corresponding function(present in the same event) has to be fetched (i use regex to extract the function)
(this will be applicable for a and b cases

Re: Analysis using lookup file

renjith_nair — Wed, 23 Dec 2020 08:23:39 GMT

The same logic explained above can be used. Let me try to explain that

Get events from index
Stats count by service and add a field source="events"
Append the inputlookup results to the event and add a field source="lookup"
Fillnull count with 0 for the lookup
Eventstats values(source) as source
From the source field, you will be able to identify whether the entry is present / not present / source

with a dummy search

Here is again a run anywhere example

Let me know the changes you want from the above search