Splunk Search

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

akarivaratharaj
Communicator

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

rvany
Communicator

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?

0 Karma

cmerriman
Super Champion

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.

0 Karma

akarivaratharaj
Communicator

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.

0 Karma

rvany
Communicator

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?

0 Karma

akarivaratharaj
Communicator

@rvany.. Yes exactly

0 Karma

akarivaratharaj
Communicator

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

0 Karma

rvany
Communicator

If I could I already would have... 😉

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...