convert the SQL query to Splunk search

[saranya]

SELECT COUNT(a.service_number)
FROM valid_services a,
dashboard b
WHERE a.service_number = b.service_number;

How to retrieve service_number from two different files having common values. Can you pls convert the above query to Splunk search and reply ASAP.

[wagnerbianchi]

Did you try to find what events from each file have in common as when you join tables using SQL?

[saranya]

I have two files and Service_number is the common field in both files. Values in this filed are also similar.
Example:
a.Service_number b.Service_number
11065 11068
11066 11067
11067 11066

I need the output of events with matching values. Say in this example, only output with 11066 and 11067.

[jrodman]

It's hard to answer this outright, in SQL there are some presumptions that the datasets are relatively finite (with a lot of optimizations to scale a fair ways) while in splunk you get a fairly direct control over using disparate data stores.

Events are effective for traversing very large amounts of unstructured data, filtering on keywords, time, and fields. Enrichment tables can be readily stored as lookups. Structured tables that want to be queried at scale can be created with tsidxstats (still a bit unpolished at the moment).

I think for this case you have a valid_services table with a pretty finite set of entries. Probably you want to render this as a lookup so that you can enrich your events which have service numbers with the service names.

But I can't really say, because the SQL statement doesn't implicitly state the contents of the tables and what it's trying to achieve. So that's my best guess.

Assuming I'm right the search would end up, after creating the lookup, being something like

get my events | lookup ServicesLookup service_number OUTPUT service_name

where "get my events" is your search terms, ServicesLookup is the name of your lookup, service_number is the field in your data, and service_name is what we're retreiving from the lookup table.