Splunk Search

Hello Experts, I would like to take report for employees who are completed four different certification courses

Ashwini_5
Explorer

I would like to take report for employees who are completed four different certification courses from my data. 

For example: Employee 1 completed 3 courses , Employee 2 completed 2 courses , Employee 3 completed 1 courses etc.. along with it's name and completion date. 

Kindly suggest how to write query in this situation. Is it ok to create CSV file with one set results and compare it or any other way available.  

Labels (2)
Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @johnhuang,

I think that the better approach is to create a lookup with your csv and then use it to enrich your search, instead using the join command that's very slow and has the limit of 50,000 results.

Remember that Splunk isin't a DB so the join command is useful when you haven't another solution!

Instead, you can use the lookup command that's similar to a left join.

So please try something like this:

index="UAT_it" sourcetype="training_api"
| spath id 
| rename id as ID
| stats max(match) AS match by ID
| search match=1 certificate_name=*
| lookup report.csv ID OUTPUT version, certificate_name, status
| table ID, version, certificate_name, status

Ciao.

Giuseppe

View solution in original post

Ashwini_5
Explorer

Hi @gcusello , sorry .. I missed to explain briefly. 

Actually, Below is the query which I am trying ..

| inputcsv report.csv |table ID |join type=outer max=5000 ID [search index="UAT_it" sourcetype="training_api" |spath id |rename id as ID |table ID,version, certificate_name ,status] 

Here, splunk has already 100000 employee details. But in that I need only 5000 set of employee details . So, I have created CSV file with required ID and trying to join with it. But, It has not worked. Could you please help.. 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Don't show us what you're doing, tell us what your data looks like.

For now we know that you have some index containing some form of structured data (judging by spayh use). What does that index contain? What data you want to retrieve from that?

You also have some lookup. What does it contain?

0 Karma

johnhuang
Motivator

| inputcsv report.csv | eval match=1 | table ID match
| append [search index="UAT_it" sourcetype="training_api"
| spath id | rename id as ID
| table ID, version, certificate_name, status]
| eventstats max(match) AS match by ID
| search match=1 AND certificate_name=*
| table ID, version, certificate_name, status

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @johnhuang,

I think that the better approach is to create a lookup with your csv and then use it to enrich your search, instead using the join command that's very slow and has the limit of 50,000 results.

Remember that Splunk isin't a DB so the join command is useful when you haven't another solution!

Instead, you can use the lookup command that's similar to a left join.

So please try something like this:

index="UAT_it" sourcetype="training_api"
| spath id 
| rename id as ID
| stats max(match) AS match by ID
| search match=1 certificate_name=*
| lookup report.csv ID OUTPUT version, certificate_name, status
| table ID, version, certificate_name, status

Ciao.

Giuseppe

johnhuang
Motivator

@gcusello,

Agreed but I'm not the OP, did not recommend join, maybe you've mistaken? My response is withinin the constraints and limited information that the OP provided. 

My understanding was that: 

  • report.csv contains a list of 5000 IDs for the purpose excluding a larger population on the main dataset (not enrichment).
  • report.csv was saved as an inputcsv and wont work as a lookup unless it's moved/configured.
  • used eventstats instead of stats in the event that the inscope ID contains multiple records since OP mention there are multiple certifications per ID.
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Ashwini_5,

your request is just a little vague because you should share informations about the data flow:

  • which sourcetype have you?
  • whick fields describe course status?

Now I can try to suppose that:

  • you have all the data in an index called "training",
  • you have all the data with a sourcetype called "courses",
  • the field containing the use account is "user_account",
  • the field containing the course Id code is "course_id",
  • the field containing the course title is "course_title",
  • the field containing the course status is "course_status",
  • a training course can be in two statuses:
    • initiated,
    • completed,
  • the field containing the course data is "course_data" and it has in the following format (YYYY-mm-dd).

With these hypothesis you could try a search like this:

index=training sourcetype=courses
| stats 
     values(course_title) AS course_title
     values(course_status) AS course_status
     dc(course_status) AS dc_course_status
     min(course_data) AS starting_data
     max(course_data) AS ending_data
     BY user_account course_id

Ciao.

Giuseppe

Ashwini_5
Explorer

Hello all, Can anyone provide suggestion on this please 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...