I have a lookup file assignment_schedule containing below sample data
assignment_group task_order schedule available_mins_weekdays available_mins_weekends
rxpress Ecommerce Avail:24x7 Maint:N/A 7200 2880
I need to evaluate the number of available mins in a month and for that have another lookup , calculation will be (available_mins_weekdays/5)*weekdays
month saturday sunday weekdays days
Jan 4 5 22 31
I expect data to be in form
assignment_group month available mins
abc Jan 22*(7200/5)
I am trying below query but it is not giving me results
|inputlookup assignment_schedule
| join type=outer assignment_group [search index="JUNK" sourcetype=applications [search index=test source=month_data.csv| eval wk_min=(available_mins_weekdays/5)*weekdays ]
In general, here's how to do a cross join in splunk...
your first search or file ...
| eval CrossJoinMe = 1
| join max=0 CrossJoinMe [your second search or file | eval CrossJoinMe = 1 ]
And here's your search...
| inputlookup assignment_schedule
| eval CrossJoinMe = 1
| rename COMMENT as "the above gets all the assignment groups"
| rename COMMENT as "In this case, 1 means Yes I Really Want All Records Cross Joined "
| rename COMMENT as "join every record above with every record in the other table"
| rename COMMENT as " max=0 also has to be set to mean Yes I Really Want All Records Cross Joined "
| join max=0 CrossJoinMe [search index=test source=month_data.csv | streamstats count as recno | eval CrossJoinMe = 1 ]
| rename COMMENT as "calculate minutes, order records and cut to desired fields"
| eval available_mins = round(weekdays*(available_mins_weekdays/5))
| sort 0 assignment_group recno
| table assignment group month available_mins
One additional note - I assumed, since you have a 3-character month, that the month records are in date order but without any year or actual date information. That order is what the | streamstats count as recno
and | sort 0 assignment_group recno
are trying to preserve. If, on the other hand, the month date is a real date, then get rid of the streamstats and change the sort to sort by the assignment group and date.
In general, here's how to do a cross join in splunk...
your first search or file ...
| eval CrossJoinMe = 1
| join max=0 CrossJoinMe [your second search or file | eval CrossJoinMe = 1 ]
And here's your search...
| inputlookup assignment_schedule
| eval CrossJoinMe = 1
| rename COMMENT as "the above gets all the assignment groups"
| rename COMMENT as "In this case, 1 means Yes I Really Want All Records Cross Joined "
| rename COMMENT as "join every record above with every record in the other table"
| rename COMMENT as " max=0 also has to be set to mean Yes I Really Want All Records Cross Joined "
| join max=0 CrossJoinMe [search index=test source=month_data.csv | streamstats count as recno | eval CrossJoinMe = 1 ]
| rename COMMENT as "calculate minutes, order records and cut to desired fields"
| eval available_mins = round(weekdays*(available_mins_weekdays/5))
| sort 0 assignment_group recno
| table assignment group month available_mins
One additional note - I assumed, since you have a 3-character month, that the month records are in date order but without any year or actual date information. That order is what the | streamstats count as recno
and | sort 0 assignment_group recno
are trying to preserve. If, on the other hand, the month date is a real date, then get rid of the streamstats and change the sort to sort by the assignment group and date.
Try this:
index="JUNK" sourcetype=applications
[search index=test source=month_data.csv | eval wk_min=(available_mins_weekdays/5)*weekdays ]
| append [|inputlookup assignment_schedule | eval sourcetype="fromInputlookup" ]
| stats values(*) AS * dc(sourcetype) AS numSourcetypes BY assignment_group
| where numSourcetypes=1
This keeps only those events which are in either source but not both (outer join).
@bic - I cleaned up the formatting and marked the code for you. I also added a closing square brace on your query, since it could not possibly run without it, but I suspect that the code isn't exactly right.
Your query is quite confusing right now. Could you provide the main search from where data will be generated? is month_data.csv your second lookup OR it's indexed data? Meanwhile give this a try (assuming month_data.csv is second lookup)
index="JUNK" sourcetype=applications
| lookup assignment_schedule assignment_group OUTPUT available_mins_weekdays
| eval month=strftime(_time,"%b")
| lookup month_data.csv month OUTPUT weekdays
| eval wk_min=(available_mins_weekdays/5)*weekdays
| table assignment_group month wk_min ...other fields...