topic Re: Complex search with set operation in Splunk Search

Complex search with set operation

bowesmana — Mon, 28 Sep 2020 15:34:48 GMT

I have 3 sourcetypes representing learners, courses and course completion details

learners - the learner details
catalog - the total course catalog for each learner
completions - the course completions for the learer

Amongst other fields,

"learners" contains Username, Name
"catalog" contains Username, Asset_Title
"completions" contains Username, Asset_Title

I want to produce a report showing each learner, the total number of courses in their catalog and the total number of courses they have completed along with a % completion. The following search almost gets there.

| set intersect [ search sourcetype=catalog* | fields Asset_Title, Username | fields - _* ] [ search sourcetype=completions* | fields Asset_Title, Username | fields - _* ] | eventstats dc(Asset_Title) as Count by Username | join Username [ search sourcetype=learners* ] | stats Count by Name

but I can't see how to get the total courses from the catalog for each learner to be retained as a field after the set operation, so I can add that as a Total field and calculate the %.

Re: Complex search with set operation

lguinn2 — Mon, 06 Jan 2014 05:37:13 GMT

Why not this? It seems much more simple

Username=* 
| stats count(eval(match(sourcetype,"catalog.*"))) as Catalog  count(eval(match(sourcetype,"completion.*"))) as Completed first(Name) as Name by Username

Although it looks like you expect to see duplicate Asset_Titles, so maybe this would work better

sourcetype=learners
| append [ search sourcetype=catalog | stats dc(Asset_Title) as CatalogCount by Username ]
| append [ search sourcetype=completions | stats dc(Asset_Title) as CompletionCount by Username ]
| stats sum(CatalogCount) as Total sum(CompletionCount) as Completed first(Name) as Name by Username
| eval PercentCompleted=round(Completed * 100 / Total, 2)
| table Name Username Completed Total PercentCompleted
| sort Name

I think either of these will run faster and be less complex than all the joins...

Re: Complex search with set operation

bowesmana — Mon, 28 Sep 2020 15:35:02 GMT

Thanks iguinn! The first didn't work - I actually have a couple of variants of sourcetype, i.e. I have completions_standard and completions_script, so I normally use completions* but the stats command doesn't like count(sourcetype=completions*).

However, the second seems to work - although it needed the 'search' in the append subsearch, i.e.

append [ search sourcetype...

Re: Complex search with set operation

lguinn2 — Mon, 06 Jan 2014 08:11:55 GMT

Sorry about the typo in the append command - I corrected my answer. Thanks!

Re: Complex search with set operation

lguinn2 — Mon, 06 Jan 2014 22:07:00 GMT

Bah - I just figured out another problem with my first search...

Re: Complex search with set operation

bowesmana — Mon, 06 Jan 2014 23:02:33 GMT

Hi Iguinn, one thing I don't understand about Splunk is how the append works to append the CatalogCount and CompletionCount values to the right learner result. How is it that the CatalogCount by Username will get correctly associated with the relevant Username record from the sourcetype=learners.

I'm still missing something fundamental in how the search pipeline works I think. Can you clarify how this works.

Thanks!

Re: Complex search with set operation

lguinn2 — Mon, 06 Jan 2014 23:57:23 GMT

Try running each of these searches separately:

sourcetype=catalog | stats dc(Asset_Title) as CatalogCount by Username

sourcetype=completions | stats dc(Asset_Title) as CompletionCount by Username

sourcetype=learners | table Username Name

Then think about how you would combine these into one big table - maintaining a row for each event (some columns would be empty).
Now, with your table, imagine doing the following stats command:

stats sum(CatalogCount) as Total sum(CompletionCount) as Completed first(Name) as Name by Username

which creates a single row for each Username

Re: Complex search with set operation

lguinn2 — Mon, 06 Jan 2014 23:58:20 GMT

Hopefully that helps. Or just run the first line of the search. Then run the first two lines of the search and see what happens. Then run the first 3 lines of the search... etc. etc.

Re: Complex search with set operation

bowesmana — Tue, 07 Jan 2014 02:22:38 GMT

Thanks, the 'big table' concept got it for me. I had tried the separate searches, but didn't understand the blank entries in the results after the appends, but when putting that into a table, I worked it out.

You've been a great help, you've got me over a mental hurdle. Thanks!