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?
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.
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.
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!
OK, be sure to click Accept
to close the question.
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. 🙂
if you please provide sample data, we could write it. Hopefully this can be done in 1 SPL
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