Splunk Enterprise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

Customized Query: How to combine 2 queries?

vishwa
Path Finder
‎11-19-2022 02:10 PM

Hi All, I am trying to combine 2 queries to get the result, i am getting the result, but not as expected.

Query1:

index=app-map-idx   shname=niht_map_*
| append [| inputlookup   customerdata.csv
|addinfo
| where  _time>=info_min_time AND _time<=info_max_time 
|fields - info*
|eval done=1, shname=device."_name",  source=device."_name"]
|table _time   sys operation web pass fail fvb  quantity done shname
|eventstats sum(done) as done by shname sys
|bucket _time span=w
|stats sum(*) as * values(done) as done dc(web) as webb by _time shname  sys operation
|appendpipe [| search sys="wi lapcdmb" operation="login page" OR operation="app page" OR operation="userpage" OR operation="custpage"]
|foreach pass fail  fvb  quantity [|eval  <<FIELD>>=<<FILED>>*0.09]
|eval sys="pim"]
| eval  "pass percentage"=pass/quantity*100
|eval iel=fail/quantity*100
|fillnull fvb value=0
|rename  pass as Pass,   fail as Fail,  sys as Sys,  fvb as Fvb,   quantity as Quantity,  operation as Operation,  webb as Webb
|lookup application.csv Sys  OUTPUT   App  mtr
|search mtr=given
|table  _time pass percentage iel App Sys  Operation Webb Pass Fail Quantity Fvb  
|sort 0 Sys

Output:

_time App Sys Operation Webb Pass Fail Quantity Fvb pass percentage iel
2022-11-10 Custom Customer sys sigin app - customer 1 20 0 20 1 100 0
2022-11-10 Cli app CA sigin app - application 2 30 10 40 10 99 0.01
2022-11-10 sigin app SG sigin app - client 2 10 1 11 1 100 0
2022-11-10 user app UA sigin app - app1 1 60 0 60 16 80 0.05
2022-11-10 user app UA sigin app - app2 4 50 0 50 0 20 0.9
2022-11-10 user app UA sigin app - app3 5 100 9 109 0 0 0
2022-11-10 user app UA sigin app - app4 8 21 0 21 0 0 0
2022-11-10 user app UA sigin app - app5 9 23 0 23 0 0 0
2022-11-10 suzk app SA sigin app - app6 2 16 8 24 1 99 0
2022-11-10 qualis app QA sigin app - app7 1 100 10 110 8 10 0
2022-11-10 benz app BA sigin app - app8 2 500 11 511 6 20 0
2022-11-10 client app CA sigin app - app1 4 200 0 200 4 100 0
2022-11-10 client app CA sigin app - app2 1 800 1 801 1 100 0.11
2022-11-10 client app CA sigin app - app3 3 220 1 221 0 90 0.22
2022-11-10 client app CA sigin app - app4 2 1000 11 1011 0 99 0.45
2022-11-10 client app CA sigin app - app5 1 1089 12 2178 9 99 0.56



Query2:
index=blrm-app-idx  sourcetype=blrm_appl  "QA: ab"  OR "QA: cd"  OR "QA: ef"  OR "QA: gh" (procedure=POST web="/cate/surface*")  OR  (procedure=GET web="/choc/plain/otp*")  OR  (procedure=POST web="/may/duration")  OR  (procedure=GET web="/year/days")  OR   web="/nam/power/error")  OR  web="/move/cloud")  OR   web="/mont/days
NOT [|inputlookup  angryer.csv
|rename PI as result
|fields result]
| eval  type=case((faildigit=978 OR faildigit=435 OR faildigit=543 OR faildigit=987), "error",
(faildigit>98763 AND faildigit<98765 )  OR faildigit=123 OR faildigit=456 OR faildigit=789,  fvb,  isnull(faildigit), "pass",   isnotnull(faildigit), "error")
|search pipe = "error"
|eval operation=case(match(web,  "/cate/surface*"),  sigin app - customer,   procedure=GET AND  web="/choc/plain/otp*") ,  sigin app - application,   procedure=POST AND web="/may/duration",  sigin app - client,   procedure=GET  AND web="/year/days", sigin app - app1,  OR   web="/nam/power/error",  sigin app - app2  OR  web="/move/cloud", sigin app - app3 OR   web="/mont/days, sigin app - app4,  1=1, "unknown")
|eval  web = procedure." ".web
|eval sys= case (cliapp=ab,  applibase,   cliapp=cd,  cusbase,    cliapp=ef,  efffilm,   cliapp=gh    gohome,  1=1, null())
|bin _time span=1h
|stats dc(cust_ip) as effect_apps  by _time sys operation web

OUTPUT:

_time sys operation web effect_apps
2022-11-10 UA sigin app - app1 1 10
2022-11-10 UA sigin app - app2 2 12
2022-11-10 UA sigin app - app3 2 10
2022-11-10 UA sigin app - app4 1 40
2022-11-10 UA sigin app - app5 4 2
2022-11-10 CA sigin app - app1 5 6
2022-11-10 CA sigin app - app2 8 1
2022-11-10 CA sigin app - app3 9 3
2022-11-10 CA sigin app - app4 2 4
2022-11-10 CA sigin app - app5 1 8



Labels (2)
0 Karma
Reply

vishwa
Path Finder
‎11-19-2022 02:11 PM

Combined 2 queries using appencols:
index=app-map-idx   shname=niht_map_*
| append [| inputlookup   customerdata.csv
|addinfo
| where  _time>=info_min_time AND _time<=info_max_time 
|fields - info*
|eval done=1, shname=device."_name",  source=device."_name"]
|table _time   sys operation web pass fail fvb  quantity done shname
|eventstats sum(done) as done by shname sys
|bucket _time span=w
|stats sum(*) as * values(done) as done dc(web) as webb by _time shname  sys operation
|appendpipe [| search sys="wi lapcdmb" operation="login page" OR operation="app page" OR operation="userpage" OR operation="custpage"]
|foreach pass fail  fvb  quantity [|eval  <<FIELD>>=<<FILED>>*0.09]
|eval sys="pim"]
| eval  "pass percentage"=pass/quantity*100
|eval iel=fail/quantity*100
|fillnull fvb value=0
|rename  pass as Pass,   fail as Fail,  sys as Sys,  fvb as Fvb,   quantity as Quantity,  operation as Operation,  webb as Webb
|lookup application.csv Sys  OUTPUT   App  mtr
|search mtr=given
|table  _time pass percentage iel App Sys  Operation Webb Pass Fail Qyantity Fvb  
|sort 0 Sys

|appendcols

[search  index=blrm-app-idx  sourcetype=blrm_appl  "QA: ab"  OR "QA: cd"  OR "QA: ef"  OR "QA: gh" (procedure=POST web="/cate/surface*")  OR  (procedure=GET web="/choc/plain/otp*")  OR  (procedure=POST web="/may/duration")  OR  (procedure=GET web="/year/days")  OR   web="/nam/power/error")  OR  web="/move/cloud")  OR   web="/mont/days
NOT [|inputlookup  angryer.csv
|rename PI as result
|fields result]
| eval  type=case((faildigit=978 OR faildigit=435 OR faildigit=543 OR faildigit=987), "error",
(faildigit>98763 AND faildigit<98765 )  OR faildigit=123 OR faildigit=456 OR faildigit=789,  fvb,  isnull(faildigit), "pass",   isnotnull(faildigit), "error")
|search pipe = "error"
|eval operation=case(match(web,  "/cate/surface*"),  sigin app - customer,   procedure=GET AND  web="/choc/plain/otp*") ,  sigin app - application,   procedure=POST AND web="/may/duration",  sigin app - client,   procedure=GET  AND web="/year/days", sigin app - app1,  OR   web="/nam/power/error",  sigin app - app2  OR  web="/move/cloud", sigin app - app3 OR   web="/mont/days, sigin app - app4,  1=1, "unknown")
|eval  web = procedure." ".web
|eval sys= case (cliapp=ab,  applibase,   cliapp=cd,  cusbase,    cliapp=ef,  efffilm,   cliapp=gh    gohome,  1=1, null())
|bin _time span=1h
|stats dc(cust_ip) as effect_apps  by _time sys operation
|appendpipe
[|lookup application.csv  App OUTPUT App mtr |search mtr=given]] 

OUTPUT:
Here i want to getting the effect_values from 2 query and not displaying in correct place.
I want the effect_apps values to be displayed by operation like:
under UA Sys , we have sigin app 

_timeAppSysOperationWebbPassFailQuantityFvbpass percentageieleffect_apps
2022-11-10CustomCustomer syssigin app - customer1200201100010
2022-11-10Cli appCAsigin app - application230104010990.0112
2022-11-10sigin appSGsigin app - client2101111100010
2022-11-10user appUAsigin app - app116006016800.0540
2022-11-10user appUAsigin app - app24500500200.92
2022-11-10user appUAsigin app - app3510091090006
2022-11-10user appUAsigin app - app48210210001
2022-11-10user appUAsigin app - app59230230003
2022-11-10suzk appSAsigin app - app621682419904
2022-11-10qualis appQAsigin app - app711001011081008
2022-11-10benz appBAsigin app - app82500115116200 
2022-11-10client appCAsigin app - app14200020041000 
2022-11-10client appCAsigin app - app21800180111000.11 
2022-11-10client appCAsigin app - app3322012210900.22 
2022-11-10client appCAsigin app - app4210001110110990.45 
2022-11-10client appCAsigin app - app5110891221789990.56 



Tags (1)
0 Karma
Reply

vishwa
Path Finder
‎11-19-2022 02:12 PM

But i need to get the output as shown  in the below format:

OUTPUT:
Here i am getting the effect_values from 2 query and not displaying in correct place.
I want the effect_apps values to be displayed by operation like for example
under Sys- UA  we have 5 operation sigin app - app1 ......... to  sigin app - app5,  effect_apps values should display in these places.
And in remaning places it should N/A.

_timeAppSysOperationWebbPassFailQuantityFvbpass percentageieleffect_apps
2022-11-10CustomCustomer syssigin app - customer12002011000N/A
2022-11-10Cli appCAsigin app - application230104010990.01N/A
2022-11-10sigin appSGsigin app - client21011111000N/A
2022-11-10user appUAsigin app - app116006016800.0510
2022-11-10user appUAsigin app - app24500500200.912
2022-11-10user appUAsigin app - app35100910900010
2022-11-10user appUAsigin app - app482102100040
2022-11-10user appUAsigin app - app59230230002
2022-11-10suzk appSAsigin app - app62168241990N/A
2022-11-10qualis appQAsigin app - app71100101108100N/A
2022-11-10benz appBAsigin app - app82500115116200N/A
2022-11-10client appCAsigin app - app142000200410006
2022-11-10client appCAsigin app - app21800180111000.111
2022-11-10client appCAsigin app - app3322012210900.223
2022-11-10client appCAsigin app - app4210001110110990.454
2022-11-10client appCAsigin app - app5110891221789990.568

 

Tags (1)
0 Karma
Reply

vishwa
Path Finder
‎11-20-2022 05:29 AM

hi, anyone can help me from the above posts????

0 Karma
Reply
Get Updates on the Splunk Community!

Monitoring MariaDB and MySQL

In a previous post, we explored monitoring PostgreSQL and general best practices around which metrics to ...

Financial Services Industry Use Cases, ITSI Best Practices, and More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Splunk Federated Analytics for Amazon Security Lake

Thursday, November 21, 2024  |  11AM PT / 2PM ET Register Now Join our session to see the technical ...