Analysis using lookup file

[Deepz2612]

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 

[renjith_nair]

Try

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

[Deepz2612]

@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..

[renjith_nair]

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?

[Deepz2612]

@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

[renjith_nair]

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

|makeresults|eval continent="Asia Asia Africa Europe"|makemv continent|mvexpand continent
|appendcols [|makeresults |eval country="China China Angola Germany"|makemv country|mvexpand country]
|rename COMMENT as "Created dummy events above"
|append [|inputlookup geo_attr_countries.csv  |fields country]
|fillnull continent value="NULL"
|stats count by country,continent
|eval count=if(continent=="NULL",0,count)

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

[Deepz2612]

• @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

[renjith_nair]

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

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

with a dummy search

index="your index" |stats count by service, "other fields"
|eval source="events"
|append [|inputlookup <your lookup file> |fields service|eval source="lookup" ]
|eventstats values(source) as source by service
|eval status=if(mvcount(source)>1,"Avaialble in Both",if(isnotnull(mvfind(source,"events")),"Available in Events","Available in Lookup"))
|fields - source
|fillnull count value=0
|stats sum(count) as count,values(function) as function,values(status) as status by service

Here is again a run anywhere example

| makeresults 
| eval continent="Asia Asia Africa Europe" 
| makemv continent 
| mvexpand continent 
| appendcols 
    [| makeresults 
    | eval country="China China Angola Germany" 
    | makemv country 
    | mvexpand country] 
| stats count by country,continent 
| eval source="events" 
| append 
    [| inputlookup geo_attr_countries.csv 
    | fields country 
    | eval source="lookup"] 
| eventstats values(source) as source by country 
| eval status=if(mvcount(source)>1,"Avaialble in Both",if(isnotnull(mvfind(source,"events")),"Available in Events","Available in Lookup")) 
| fields - source 
| fillnull count value=0 
| stats sum(count) as count,values(continent) as continent,values(status) as status by country

Let me know the changes you want from the above search