topic Re: How to convert this SQL query to splunk search in Splunk Search

How to convert this SQL query to splunk search

foxychen — Mon, 24 Aug 2020 02:58:32 GMT

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

Name	pid	parent
csrss.exe	568	552
csrss.exe	576	560
fontdrvhost.exe	564	756
lsass.exe	712	556
lsass.exe	728	572
services.exe	712	564
services.exe	716	568
services.exe	712	568
services.exe	836	712
services.exe	836	712
svchost.exe	712	716
wininit.exe	564	468
wininit.exe	568	472
wininit.exe	572	476
wininit.exe	712	592

SQL query:

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

Use SQL query result

Name	pid	parent
wininit.exe	564	468
fontdrvhost.exe	564	756
wininit.exe	568	472
csrss.exe	568	552
services.exe	712	564
wininit.exe	712	592
services.exe	712	568
svchost.exe	712	716
lsass.exe	712	556

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

name	pid	parent
wininit.exe	712	592
wininit.exe	712	592
wininit.exe	712	592
svchost.exe	1812	712
svchost.exe	1480	712
svchost.exe	2024	712
svchost.exe	1780	712
svchost.exe	4496	712

SQL query Results

name	pid	parent
wininit.exe	712	592
wininit.exe	712	592
wininit.exe	712	592

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!!

Re: How to convert this SQL query to splunk search

richgalloway — Fri, 21 Aug 2020 15:31:18 GMT

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.

Re: How to convert this SQL query to splunk search

foxychen — Mon, 24 Aug 2020 02:51:04 GMT

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.

Re: How to convert this SQL query to splunk search

to4kawa — Mon, 24 Aug 2020 08:59:37 GMT

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

Re: How to convert this SQL query to splunk search

foxychen — Thu, 27 Aug 2020 06:05:38 GMT

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

Re: How to convert this SQL query to splunk search

to4kawa — Wed, 26 Aug 2020 08:10:33 GMT

>The data is". jason",

JSON

what's right field names?

SQL is not related at all.

Re: How to convert this SQL query to splunk search

foxychen — Thu, 27 Aug 2020 06:06:06 GMT

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：

Name	pid	parent
csrss.exe	568	552
fontdrvhost.exe	564	756
lsass.exe	712	556
lsass.exe	728	572
services.exe	712	564
services.exe	716	568
services.exe	836	712
svchost.exe	712	716
wininit.exe	564	468
wininit.exe	568	472
wininit.exe	1712	592

Results of SQL query：

Name	pid	parent
csrss.exe	568	552
fontdrvhost.exe	564	756
lsass.exe	712	556
services.exe	712	564
svchost.exe	712	716
wininit.exe	564	468
wininit.exe	568	472

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：

Name	pid	parent	parent_id
csrss.exe	568	552
fontdrvhost.exe	564	756
lsass.exe	712	556
lsass.exe	728	572
services.exe	712	564
services.exe	716	568
services.exe	836	712
svchost.exe	712	716
wininit.exe	564	468
wininit.exe	568	472
wininit.exe	1712	592

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：

Name	pid	parent	parent_id
csrss.exe	568	552	552
fontdrvhost.exe	564	756	756
lsass.exe	712	556	556
services.exe	712	564	564
svchost.exe	712	716	716
wininit.exe	564	468	468
wininit.exe	568	472	472
wininit.exe	1712	592	592

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

Re: How to convert this SQL query to splunk search

to4kawa — Thu, 27 Aug 2020 10:19:37 GMT

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