How to pass variable or token from subsearch to search

[rsokolova]

Hi Splunkers,

We are trying to pass variables from the subsearch to search, in this case from the subsearch we are getting 3 fields which will need to be in the SQL of the search.

SubSearch results:
PO_Number=123
HOUSE_DESC=ATL
PRODUCT_ID=456

| dbxquery query="select sku from purchase_orders_line_item
where purchase_orders_id = (select purchase_orders_Id from purchase_orders where tc_purchase_orders_id = $PO_NUMBER$)
and sku = $PRODUCT_ID$ " connection=$HOUSE_DESC$_WM13 shortnames=true
| appendcols [| dbxquery query="select m.SORFID, m.requested_by, w.USER_NAME AS \"Request_by_Username\",m.approved_by,w2.USER_NAME AS \"Approved_by_Username\", m.completed_by,
p.po_number, d.product_id, d.sorf_cases, d.custid,m.requested_date , h.house_desc,m.notes
from sorfmain m
INNER JOIN sorfpurch p ON m.sorfid=p.sorfid
INNER JOIN sorfdetail d ON p.sorfdetailid=d.sorfdetailid AND m.sorfid=d.sorfid
INNER JOIN webapps_owner.user_d w ON m.requested_by=w.user_id
INNER JOIN webapps_owner.user_d w2 ON m.approved_by = w2.user_id
INNER JOIN DW_MAIN.house h ON h.house_id=d.house_id and m.requested_date > sysdate - 5 and h.house_desc='ATL' and m.SORFID='132390' " connection=DW_PROD shortnames=true
| join type=left Request_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName) | rename sAMAccountName AS Request_by_Username, mail as R.Mail, sn AS R.LName, givenName AS R.FName | table R.Mail, Request_by_Username ]
| join type=left Approved_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName) | rename sAMAccountName AS Approved_by_Username, mail as A.Mail, sn AS A.LName, givenName AS A.FName | table A.Mail, Approved_by_Username ] ]

Thanks in advance,

[omuelle1 · Accepted solution]

Hi there,

try this here using the map command should do it:

| dbxquery query="select m.SORFID, m.requested_by, w.USER_NAME AS \"Request_by_Username\",m.approved_by,w2.USER_NAME AS \"Approved_by_Username\", m.completed_by,
          p.po_number, d.product_id, d.sorf_cases, d.custid,m.requested_date , h.house_desc,m.notes
                from sorfmain m 
   INNER JOIN sorfpurch p ON m.sorfid=p.sorfid 
   INNER JOIN sorfdetail d ON p.sorfdetailid=d.sorfdetailid AND m.sorfid=d.sorfid
   INNER JOIN webapps_owner.user_d w ON m.requested_by=w.user_id 
   INNER JOIN webapps_owner.user_d w2 ON  m.approved_by = w2.user_id
   INNER JOIN DW_MAIN.house h ON h.house_id=d.house_id  and  m.requested_date > sysdate - 5 and m.sorfid='132373'  "  connection=DW_PROD shortnames=true  | fields Request_by_Username PO_NUMBER PRODUCT_ID HOUSE_DESC SORFID Approved_by_Username REQUESTED_DATE NOTES SORF_CASES
|  map search="| dbxquery query=\"select * from purchase_orders_line_item 
where purchase_orders_id = (select purchase_orders_Id from purchase_orders where tc_purchase_orders_id = $PO_NUMBER$) and sku = $PRODUCT_ID$\" connection=$HOUSE_DESC$_WM13 shortnames=true | eval Request_by_Username=\"$Request_by_Username$\" | eval Sorf=\"$SORFID$\" | eval House=\"$HOUSE_DESC$\" | eval Approved_by_Username=\"$Approved_by_Username$\" | eval PO_NUMBER = \"$PO_NUMBER$\" | eval PRODUCT_ID=\"$PRODUCT_ID$\" | eval RequestDate=\"$REQUESTED_DATE$\" | eval Cases=\"$SORF_CASES$\" | eval Notes=\"$NOTES$\" |  table SKU Request_by_Username Sorf PO_NUMBER PRODUCT_ID House Approved_by_Username RequestDate Notes Cases"
| join type=left Request_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName)  | rename sAMAccountName AS Request_by_Username, mail as R.Mail, sn AS R.LName, givenName AS R.FName | table R.Mail, Request_by_Username  ]
| join type=left Approved_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName)  | rename sAMAccountName AS Approved_by_Username, mail as A.Mail, sn AS A.LName, givenName AS A.FName | table A.Mail, Approved_by_Username ]

[gcusello]

Hi rsokolova,
let me understand: you have the main query (a dbxquery query)

    | dbxquery query="select sku from purchase_orders_line_item 
    where purchase_orders_id = (select purchase_orders_Id from purchase_orders where tc_purchase_orders_id = $PO_NUMBER$)
    and sku = $PRODUCT_ID$ " connection=$HOUSE_DESC$_WM13 shortnames=true 
in which there are three tokens;

than you append events from a subsearch

    | appendcols [| dbxquery query="select m.SORFID, m.requested_by, w.USER_NAME AS \"Request_by_Username\",m.approved_by,w2.USER_NAME AS \"Approved_by_Username\", m.completed_by,
    p.po_number, d.product_id, d.sorf_cases, d.custid,m.requested_date , h.house_desc,m.notes
    from sorfmain m 
    INNER JOIN sorfpurch p ON m.sorfid=p.sorfid 
    INNER JOIN sorfdetail d ON p.sorfdetailid=d.sorfdetailid AND m.sorfid=d.sorfid
    INNER JOIN webapps_owner.user_d w ON m.requested_by=w.user_id 
    INNER JOIN webapps_owner.user_d w2 ON m.approved_by = w2.user_id
    INNER JOIN DW_MAIN.house h ON h.house_id=d.house_id and m.requested_date > sysdate - 5 and h.house_desc='ATL' and m.SORFID='132390' " connection=DW_PROD shortnames=true
    | join type=left Request_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName) | rename sAMAccountName AS Request_by_Username, mail as R.Mail, sn AS R.LName, givenName AS R.FName | table R.Mail, Request_by_Username ]
    | join type=left Approved_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName) | rename sAMAccountName AS Approved_by_Username, mail as A.Mail, sn AS A.LName, givenName AS A.FName | table A.Mail, Approved_by_Username ] ]
And you want to pass the three tokens from the append subsearch to the main search?

If this is your need, you cannot pass a token or a field from an append subsearch to the main search, eventually you can pass fields (with the same name) using the subsearch in the main search, in other words, something like this:

| dbxquery query="select sku from purchase_orders_line_item 
where purchase_orders_id = (select purchase_orders_Id from purchase_orders where tc_purchase_orders_id = $PO_NUMBER$)
and sku = $PRODUCT_ID$ " connection=$HOUSE_DESC$_WM13 shortnames=true 
[ | dbxquery query="select m.SORFID, m.requested_by, w.USER_NAME AS \"Request_by_Username\",m.approved_by,w2.USER_NAME AS \"Approved_by_Username\", m.completed_by,
p.po_number, d.product_id, d.sorf_cases, d.custid,m.requested_date , h.house_desc,m.notes
from sorfmain m 
INNER JOIN sorfpurch p ON m.sorfid=p.sorfid 
INNER JOIN sorfdetail d ON p.sorfdetailid=d.sorfdetailid AND m.sorfid=d.sorfid
INNER JOIN webapps_owner.user_d w ON m.requested_by=w.user_id 
INNER JOIN webapps_owner.user_d w2 ON m.approved_by = w2.user_id
INNER JOIN DW_MAIN.house h ON h.house_id=d.house_id and m.requested_date > sysdate - 5 and h.house_desc='ATL' and m.SORFID='132390' " connection=DW_PROD shortnames=true
| join type=left Request_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName) | rename sAMAccountName AS Request_by_Username, mail as R.Mail, sn AS R.LName, givenName AS R.FName | table R.Mail, Request_by_Username ]
| join type=left Approved_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName) | rename sAMAccountName AS Approved_by_Username, mail as A.Mail, sn AS A.LName, givenName AS A.FName | table A.Mail, Approved_by_Username ] | rename tc_purchase_orders_id AS p.po_number sku AS d.product_id | fields tc_purchase_orders_id sku ]
| ...

In this way you use tc_purchase_orders_id and sku from the results of the subsearch to filter the main search (beware that field names must be the same in main and sub search and that you pass only the fields to use as search parameters not all the fields!).

I don't think that it's possible to pass also the connection value!

Anyway, if it's correct (I'm not sure!), this search will surely be very very slow!
so probably you have to design it in a different way:

• don't use dbquery command,
• but schedule your queries to extract events from your DB and ingest them in Splunk every hour (or day, or the period you want),
• then you can correlate them in Splunk passing parameters from subsearch to main search.

I hope to be clear in my description!

Bye.
Giuseppe

[rsokolova]

Thanks for advises and replying back...... We tried what you have suggested with simple search and it doesn't seem to work unless I've missed something. See below.

| dbxquery query="select * from purchase_orders_line_item
where purchase_orders_id = (select purchase_orders_Id from purchase_orders where tc_purchase_orders_id = '145062')
and sku = '48036' " connection=$house$ shortnames=true [ | makeresults | eval house="ATL_WM13" ]

Error: External search command 'dbxquery' returned error code 1. Script output = "HTTPError: HTTP 404 Not Found -- Could not find object id=$house$

As you can see in the error, it's not passing the variable from the subsearch to the search, however if I try using the command "return" it does return a value, but its not what we need.

Thanks for looking at this Giuseppe

[gcusello]

As I said, it's not possible to pass the connection as a variable!
For this reason I suggested to find a easier query to run to ingest logs in Splunk and then correlate them.

Bye.
Giuseppe