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

[Ashwini_5]

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.  

[Ashwini_5]

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.. 

[PickleRick]

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?

[johnhuang]

| 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

[gcusello · Accepted solution]

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]

@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.

[gcusello]

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]

Hello all, Can anyone provide suggestion on this please