How to join a search query and a lookup file without any common columns and display the output

[akarivaratharaj]

I have a below search query which gives me the count of the error(the corresponding events have only the description of the error and there is no field/column name in the events)

index="x" sourcetype="y" |eval SERVICE="MSSQL"| stats count as Error by SERVICE

I have a lookup file which have the release version with its corresponding start and end date.

I want to join the search query and the lookup file and need the result as below:

SERVICE RELEASE_VERSION START_DATE END_DATE ERROR
MSSQL 2.5 DD/MM/YY DD/MM/YY 90
MSSQL 2.6 DD/MM/YY DD/MM/YY 70

Could anyone please help me on this.

[DalJeanis]

One off-topic suggestion - You should ALWAYS try to train your users to accept dates in this order (YYYY-MM-DD or YYYY/MM/DD). It will save you decades of headaches, because of its advantages - (1) It is unambiguous everywhere in the world. (2) Everyone will always read it correctly. (3) It will sort and compare directly without any conversion.

[rvany]

You set SERVICE to a constant value and count over this value (i.e. "MSSQL") which gives you a count of all your events. So what do you want to join?

[cmerriman]

it would look something like this:

index="x" sourcetype="y" |eval SERVICE="MSSQL"| stats count as ERROR by SERVICE|join type=left SERVICE [|inputlookup servicelookupfile.csv|fields SERVICE RELEASE_VERSION START_DATE END_DATE]|fields SERVICE RELEASE_VERSION START_DATE END_DATE ERROR

given that the lookup has SERVICE and nothing needs renaming. added type=left into the join in case some aren't in the lookup.

[akarivaratharaj]

Hi @cmerriman,

Thanks for the response.
Here what I am expecting is the error count of MSSQL should display respective to the dates. For example, the version 2.5 has been used between 01/01/2016 and 06/01/2016. So during that period of time how many errors have occured. Like that from 06/01/2016 to 01/12/2016, say its version 2.6, so how many errors occured during that period until the next version comes.

[rvany]

So am I right: you want to correlate the MSSQL-version with your events based on the event's timestamp matching an intervall from START_DATE to END_DATE of your lookup?

[akarivaratharaj]

@rvany.. Yes exactly

[akarivaratharaj]

@rvany... could you suggest me on the above concept.

[rvany]

If I could I already would have... 😉

But I will try. Could you send an example of your lookup file?