Splunk Search

Use transaction command to process data and get 3 different results? Is this even possible??

timothytruax
Explorer

Here is what I have: ...a log table with a unique FName-LName & Job-Title.
I pulled 100 rows on both yesterday and 100 today so there are 2 different dates for each set of 100 rows.

• I want to generate a report that will list any people that are in the current-days pull that are not found in yesterday's pull. (New people)
• I also want to list any people that are in yesterday's pull but are not found in the current-day's pull. (People that left)
• I also want to pull any people in the current day's pull that have a different Job-Title from yesterday's pull. (Promoted or demoted people).

Can this be done in 1 SPL setup? Or must it be 3 separate pulls?

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo ((earliest=-1d@d latest=@d-1) OR (earliest=@d))
| bin _time span=1d
| stats dc(_time) AS timeCount list(_time) AS times dc(Job-Title) AS JobCount list(Job-Title) AS JobTitles BY "FName-LName"
| multireport
[ where timeCount==1 AND times >= relative_time(now(), "@d")
| eval type = "hired"]
[ where timeCount==1 AND times < relative_time(now(), "@d")
| eval type = "fired"]
[ where JobCount>1
| eval type = "changed"]

P.S. Field names with hyphens are evil and transaction is to be avoided at all costs.

View solution in original post

woodcock
Esteemed Legend

Like this:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo ((earliest=-1d@d latest=@d-1) OR (earliest=@d))
| bin _time span=1d
| stats dc(_time) AS timeCount list(_time) AS times dc(Job-Title) AS JobCount list(Job-Title) AS JobTitles BY "FName-LName"
| multireport
[ where timeCount==1 AND times >= relative_time(now(), "@d")
| eval type = "hired"]
[ where timeCount==1 AND times < relative_time(now(), "@d")
| eval type = "fired"]
[ where JobCount>1
| eval type = "changed"]

P.S. Field names with hyphens are evil and transaction is to be avoided at all costs.

timothytruax
Explorer

Woocock!

You're a Splunk Ninja and a genius too!

Thank you so much.

Solved my issue and your SPL contruct has taught me a good deal. 🙂

Below is the actual data I used - ingesting on 2 different days!
••••••••••••••••••••••••••••••••••••••••••••
Day 1 ---
FnameLname,JobTitle,IngestDate
tim ham,driver,05-08-2019
tom hem,driver,05-08-2019
tod harm,driver,05-08-2019
jim slump,driver,05-08-2019
john hill,driver,05-08-2019
fill billy,driver,05-08-2019
slim pickins,driver,05-08-2019
jill hill,driver,05-08-2019
betty mids,driver,05-08-2019
rich farm,driver,05-08-2019
••••••••••••••••••••••••••••••••••••••••••••
Day 2
FnameLname,JobTitle,IngestDate
tim ham,driver,05-09-2019
tom hem,fast-driver,05-09-2019
tod harm,driver,05-09-2019
jim slump,driver,05-09-2019
rick hull,driver,05-09-2019
fill billy,slow-driver,05-09-2019
slim pickins,driver,05-09-2019
jill hill,driver,05-09-2019
betty mids,driver,05-09-2019
rich farm,driver,05-09-2019
lacey underalls,farm-driver,05-09-2019
••••••••••••••••••••••••••••••••••••••••••••
Your modified SPL (Works Great!)
index="table4work_all" ((earliest=-1d@d latest=@d-1) OR (earliest=@d))
| bin _time span=1d
| stats dc(_time) AS timeCount values(_time) AS times dc(JobTitle) AS JobCount values(JobTitle) AS JobTitles BY FnameLname
| multireport
[ where timeCount==1 AND times >= relative_time(now(), "@d")
| eval type = "hired"]
[ where timeCount==1 AND times < relative_time(now(), "@d")
| eval type = "fired"]
[ where JobCount>1
| eval type = "changed"]

Thank you!

0 Karma

woodcock
Esteemed Legend

OK, be sure to click Accept to close the question.

0 Karma

timothytruax
Explorer

Thank you Woodcock for your quick answer!

I am going to try this and see if it works.

I will thumbs up you when done. 🙂

0 Karma

koshyk
Super Champion

if you please provide sample data, we could write it. Hopefully this can be done in 1 SPL

0 Karma

timothytruax
Explorer

Hello Koshyk -
Thank you for answering. 🙂
See sample csv type data I put together quickly below; you can copy the small table and paste into Notetab as a .txt type and then ingest. Its not big but can be used to prove that your SPL works.

---------- Begin table

tim ham,driver,05-08-2019
tom hem,driver,05-08-2019
tod harm,driver,05-08-2019
jim slump,driver,05-08-2019
john hill,driver,05-08-2019
fill billy,driver,05-08-2019
slim pickins,driver,05-08-2019
jill hill,driver,05-08-2019
betty mids,driver,05-08-2019
rich farm,driver,05-08-2019
tim ham,driver,05-09-2019
tom hem,fast-driver,05-09-2019
tod harm,driver,05-09-2019
jim slump,driver,05-09-2019
rick hull,driver,05-09-2019
fill billy,slow-driver,05-09-2019
slim pickins,driver,05-09-2019
jill hill,driver,05-09-2019
betty mids,driver,05-09-2019
rich farm,driver,05-09-2019
lacey underalls,farm-driver,05-09-2019

---------- End table

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...