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

[timothytruax]

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?

[woodcock · Accepted solution]

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]

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!

[woodcock]

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

[timothytruax]

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

[koshyk]

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

[timothytruax]

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