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.
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.
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?
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.
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.
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?
@rvany.. Yes exactly
@rvany... could you suggest me on the above concept.
If I could I already would have... 😉
But I will try. Could you send an example of your lookup file?