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 Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

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