Splunk Search

How do I split a SQL statement's query fields and relate each field with the app name?

rilee
Explorer

I have a search result like below:

[-]
   dt2021-06-24T22:46:40.7013297Z
   flds: [ [-]
     { [-]
       fnusername
       nv: LearningRegApplication
     }
     { [-]
       fndbQueries
       nvSQL_QUERIES=Select emp.fieldA, emp.fieldB, emp.fieldC, emp.fieldD from Template.table emp WHERE (UPPER(emp.fieldA) = UPPER(:emp.fieldA)) AND (UPPER(emp.fieldB) = UPPER(:emp_fieldB)) AND (UPPER(emp.fieldC) IN (UPPER('aaa'), UPPER('bbb'), UPPER('ccc'), UPPER('ddd')))
     }
   ]
   sfEmployeeLogic
   sidT1-SECURITY-{A-8FE-76E9-C3A2-ED890B}
   smGetAsync
   statSuccess
   tidyb6Any-PG00IG53
}

I used   |SPATH OUTPUT=status PATH=stat | SPATH OUTPUT=nv PATH=flds{}.nv | SPATH OUTPUT=fn PATH=flds{}.fn | Table fn nv host status.  The results return 4 columns.  Each row has the array of the 2 sets of key-value pair in the fn/nv stacked together.   But host and status has only hostname and status values as follows:

fn            nv              host             status

(Row 1) username     LearningRegApplication                                                                                          MyHost  Success

(Row 1) dbQueries     SQL_QUERIES=Select emp.fieldA, emp.fieldB, emp.fieldC, emp.fieldD from Template.table emp WHERE (UPPER(emp.fieldA) = UPPER(:emp.fieldA)) AND (UPPER(emp.fieldB) = UPPER(:emp_fieldB)) AND (UPPER(emp.fieldC) IN (UPPER('aaa'), UPPER('bbb'), UPPER('ccc'), UPPER('ddd')))

 

And I have uploaded a CSV file that contains all query fields name.  I want to see each field was queried by which application and insert them in that field row (not sure what is the best way to present this information).  So the output may look like something below:

fieldA  LearningRegApplication

fieldB  LearningRegApplication, AThirdApp

fieldC  LearningRegApplication, ASecondApp

fieldD  LearningRegApplication

fieldE  ASecondApp, AThirdApp

fieldF  AForthApp

  :

  : 

What is the way to use the full CSV list of field names to insert the app to each SQL field name?

Labels (1)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Will some of this help?

| makeresults
| eval _raw="{
   \"dt\": \"2021-06-24T22:46:40.7013297Z\",
   \"flds\": [
     {
       \"fn\": \"username\",
       \"nv\": \"LearningRegApplication\"
     },
     {
       \"fn\": \"dbQueries\",
       \"nv\": \"SQL_QUERIES=Select emp.fieldA, emp.fieldB, emp.fieldC, emp.fieldD from Template.table emp WHERE (UPPER(emp.fieldA) = UPPER(:emp.fieldA)) AND (UPPER(emp.fieldB) = UPPER(:emp_fieldB)) AND (UPPER(emp.fieldC) IN (UPPER('aaa'), UPPER('bbb'), UPPER('ccc'), UPPER('ddd')))\"
     }
   ],
   \"sf\": \"EmployeeLogic\",
   \"sid\": \"T1-SECURITY-{A-8FE-76E9-C3A2-ED890B}\",
   \"sm\": \"GetAsync\",
   \"stat\": \"Success\",
   \"tid\": \"yb6Any-PG00IG53\"
}"



| spath output=status path=stat
| spath output=nv path=flds{}.nv
| spath output=fn path=flds{}.fn
| table fn nv host status
| eval fieldname=mvindex(fn,0)
| eval {fieldname}=mvindex(nv,0)
| eval fieldname=mvindex(fn,1)
| eval {fieldname}=mvindex(nv,1)
| fields - fn nv
| rex field=dbQueries "(?i)select\s+(?<columns>.*)\s+from"
| eval column=split(columns,", ")
| mvexpand column

View solution in original post

rilee
Explorer

Thank you for the reply. 

I understand the syntax you shared but I am not sure how to incorporate the syntax.  (Reading now more examples for |makeresults usage.)  I appended your lines after my initial lines (that yielded many rows of results):  

index="my_index"
(host="host1*" OR host="host2" OR host="host3" OR host="host4")
source="*Business.Milestones.log"
| rex field=_raw "s/^.* {/{/" mode=sed

Execution error tells me | Makeresults needs to be the first command in search.

What I showed in Json is from the first row of the returned results.  The search has many rows returned with the SQL statement dynamically generated. So the column count will vary.  Will the |Makeresults syntax able to translate that?  And I need to fill(append) the application names ((nv,0)) in each column((nv,1).  If I make the results working, how to make the metrics to summarize desired final output?  e.g.:

columnA

app1, app2, etc.
ColumnBapp2, app4, etc.

etc. As many columns

whichever apps request for whatever columns

 

Is it possible?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The first part before the blank lines uses makeresults to create some dummy data based on your post. The second part after the blank lines is the processing that would be applied to your real events. Perhaps you can share some more realistic examples of your log events so a more appropriate solution can be suggested? For events, it is more useful in you share the raw event data in a code block </> so it can be copy/pasted into a makeresults section for me to test with.

rilee
Explorer

Got it, I see something new.  One step at a time.  Just started Splunking for less than 7 days.   Hopefully one day can be as powerful as you all.  So I can also whisper without too much sweat!  ^_^ Later.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Will some of this help?

| makeresults
| eval _raw="{
   \"dt\": \"2021-06-24T22:46:40.7013297Z\",
   \"flds\": [
     {
       \"fn\": \"username\",
       \"nv\": \"LearningRegApplication\"
     },
     {
       \"fn\": \"dbQueries\",
       \"nv\": \"SQL_QUERIES=Select emp.fieldA, emp.fieldB, emp.fieldC, emp.fieldD from Template.table emp WHERE (UPPER(emp.fieldA) = UPPER(:emp.fieldA)) AND (UPPER(emp.fieldB) = UPPER(:emp_fieldB)) AND (UPPER(emp.fieldC) IN (UPPER('aaa'), UPPER('bbb'), UPPER('ccc'), UPPER('ddd')))\"
     }
   ],
   \"sf\": \"EmployeeLogic\",
   \"sid\": \"T1-SECURITY-{A-8FE-76E9-C3A2-ED890B}\",
   \"sm\": \"GetAsync\",
   \"stat\": \"Success\",
   \"tid\": \"yb6Any-PG00IG53\"
}"



| spath output=status path=stat
| spath output=nv path=flds{}.nv
| spath output=fn path=flds{}.fn
| table fn nv host status
| eval fieldname=mvindex(fn,0)
| eval {fieldname}=mvindex(nv,0)
| eval fieldname=mvindex(fn,1)
| eval {fieldname}=mvindex(nv,1)
| fields - fn nv
| rex field=dbQueries "(?i)select\s+(?<columns>.*)\s+from"
| eval column=split(columns,", ")
| mvexpand column
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...