Splunk Search

How to convert this SQL query to splunk search

foxychen
Engager

 

8/24 update

I'm sorry, I didn't describe the problem well.

I re-corrected the description.

I need to find "parent" in the processes table "services.exe",
Using the above found "parent" looking for "parent" = "pid" in the original "processes" table consistent information.

Processes table

Namepidparent
csrss.exe568552
csrss.exe576560
fontdrvhost.exe564756
lsass.exe712556
lsass.exe728572
services.exe712564
services.exe716568
services.exe712568
services.exe836712
services.exe836712
svchost.exe712716
wininit.exe564468
wininit.exe568472
wininit.exe572476
wininit.exe712592

 

SQL query:

 

SELECT name 
FROM processes 
WHERE pid=(SELECT parent FROM processes WHERE LOWER(name)='services.exe');

 

Use SQL query result

Namepidparent
wininit.exe564468
fontdrvhost.exe564756
wininit.exe568472
csrss.exe568552
services.exe712564
wininit.exe712592
services.exe712568
svchost.exe712716
lsass.exe712556

 

Use  Splunk search

 

index="processes" [search index="processes" name=services.exe | dedup parent | fields parent]
|search pid=parent
|table name parent pid 

 

but  No results !!

Please help me  convert splunk query

Thnaks!!

---------------------------------------------------------------------------


i need to convert sql query into splunk query could some one help me ?

here is SQL query:

SELECT name pid parent FROM processes WHERE pid=(SELECT parent FROM processes WHERE LOWER(name)='services.exe') ;

processes table

namepidparent
wininit.exe712592
wininit.exe712592
wininit.exe712592
svchost.exe1812712
svchost.exe1480712
svchost.exe2024712
svchost.exe1780712
svchost.exe4496712

 

SQL query Results

 
namepidparent
wininit.exe712592
wininit.exe712592
wininit.exe712592

 

I try  Splunk search:

index=" porcesses "  [search index="porcesses" columns.name=services.exe | dedup parent | fields parent ]
|search pid=parent
|table name parent pid 

 

but  No results !!

Please help me  convert splunk query

Thnaks!!

 

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

to4kawa
Ultra Champion

index=processes | eventstats values(eval(if(name="services.exe",NULL,parent))) as parent_id
| table name pid parent parent_id

| eval pids="^".pid."$"

| where match(parent_id,pids)
| fields - parent_id pids

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Have you seen this site? http://www.innovato.com/splunk/SQLSplunk.html

Your query is good, but the subsearch is unnecessary.  Also, there is no field called "column.name", but there is a "name" field.

index="processes" name="services.exe" 
| dedup parent
| table name pid parent 

 I'm not surprised this returns no results since the sample data does not contain the required "services.exe" in the name field.

---
If this reply helps you, Karma would be appreciated.
0 Karma

foxychen
Engager

Thank you for your answer.

I'm sorry, I didn't describe the problem well.

I re-corrected the description.

I need to find "parent" in the processes table "services.exe",
Using the above found "parent" looking for "parent" = "pid" in the original "processes" table consistent information.

0 Karma

to4kawa
Ultra Champion

simple:

index=" porcesses "  [search index="porcesses" name="services.exe" | dedup parent | rename parent as pid | table pid]
|table name parent pid 

 

 splunke-like:

 

index=processes | eventstats values(eval(if(name="services.exe",parent,NULL))) as parent_id
| table name pid parent parent_id
| where match(parent_id,pid)
| fields - parent_id

 

 

0 Karma

foxychen
Engager

Thank you for your answer.

I try 

 

 

index=processes | eventstats values(eval(if(name="services.exe",parent,NULL))) as parent_id
| table name pid parent parent_id
| where match(parent_id,pid)
| fields - parent_id

 

 

but "parent_id" is all NULL.

I tried to change the position of NULL

 

 

index=processes | eventstats values(eval(if(name="services.exe",NULL,parent))) as parent_id
| table name pid parent parent_id
| where match(parent_id,pid)
| fields - parent_id

 

 

All "parents_id" have data , but "match" It's not exactly the same,

The "parent_id" field and "pid" field is not fully compliant, is partially matched.

I changed "match" to "like",Only " 0" is data fully compliant.

I guess if the data types of parent and PID are different, they will not match ?

The data is". json", Splunk automatically extracts fields. I don't know the data types of the two fields

0 Karma

to4kawa
Ultra Champion

>The data is". jason",

JSON

what's right field names?

SQL is not related at all.

0 Karma

foxychen
Engager

I have an SQL database and a splunk to receive data,

Data is imported into Splunk & SQL database at the same time

I can using SQL query screening data is feasible.

use SQL query:

 

 

 

 

SELECT name 
FROM processes 
WHERE pid=(SELECT parent FROM processes WHERE LOWER(name)='services.exe');

 

 

 

 

@to4kawa 

I want to use the same criteria to filter data on Splunk.

But I don't know what to do with search commands.

"The data is .jason " → "The data is .json"  

Sorry, I misspelled!!!

It means that the data passed into Splunk is in .json file format, and the field is splunk automatically resolved.

I came up with this information ,because I'm not sure  the data type of the field will affect match ?

 

First of all, thank you very much for your help!!!

Your answer can filter out the data, But the data filtered out is not the same as that of SQL query

I found that the field "match" is a partial match, not a full match

So the data filtering is inconsistent.

Examples:

Namepidparent
csrss.exe568552
fontdrvhost.exe564756
lsass.exe712556
lsass.exe728572
services.exe712564
services.exe716568
services.exe836712
svchost.exe712716
wininit.exe564468
wininit.exe568472
wininit.exe1712592

 

Results of SQL query:

Namepidparent
csrss.exe568552
fontdrvhost.exe564756
lsass.exe712556
services.exe712564
svchost.exe712716
wininit.exe564468
wininit.exe568472

 

Use Splunk search 

index=processes | eventstats values(eval(if(name="services.exe",NULL,parent))) as parent_id
| table name pid parent parent_id
| where match(parent_id,pid)
| fields - parent_id

Results of Splunk search:

Namepidparentparent_id
csrss.exe568552 
fontdrvhost.exe564756 
lsass.exe712556 
lsass.exe728572 
services.exe712564 
services.exe716568 
services.exe836712 
svchost.exe712716 
wininit.exe564468 
wininit.exe568472 
wininit.exe1712592 

The filtered data results show all the data.

 

I'm trying to change the filters as follows.

 

 

 

 

index=processes | eventstats values(eval(if(name="services.exe",NULL,parent))) as parent_id
| table name pid parent parent_id
| where match(parent_id,pid)
| fields - parent_id

 

 

 

 

 Results of Splunk search:

Namepidparentparent_id
csrss.exe568552552
fontdrvhost.exe564756756
lsass.exe712556556
services.exe712564564
svchost.exe712716716
wininit.exe564468468
wininit.exe568472472
wininit.exe1712592592

The results of the screening are added one more red marked amount of data.

0 Karma

to4kawa
Ultra Champion

index=processes | eventstats values(eval(if(name="services.exe",NULL,parent))) as parent_id
| table name pid parent parent_id

| eval pids="^".pid."$"

| where match(parent_id,pids)
| fields - parent_id pids

0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...