Archive

evaluate values from two different lookups

Explorer

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 ]
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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.

View solution in original post

SplunkTrust
SplunkTrust

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.

View solution in original post

Esteemed Legend

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Revered Legend

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...
0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!