Splunk Search

Splunk query to exclude the searched strings based on date and display in table

asharmaeqfx
Path Finder

Hi Splukers,

I have a requirement to search for some filenames and display the missing files as per the date. Thus, i made up a query to look like

index=123 host=htrstef87 "string_1," "created" NOT client_ip="192.168.17.5" "String_examp_*" "xml.7z.pgp" | eval keyword=case(searchmatch("string_1L_2"),"string_1L_2",searchmatch("string_1L_21"),"string_1L_21",searchmatch("string_1L_22"),"string_1L_22",searchmatch("string_1L_23"),"string_1L_23",searchmatch("string_1L_24"),"string_1L_24") | eval Filestatus=if(like(keyword, "string_1L%"), "fileFound", "Filenotfound") |eval DateReport= date_month."-".date_year| stats values(keyword), values(FileName), values(Filesize) by DateReport | where Filesize>0

This displays all the filenames with all the data. But the requirement is to match the keyword and check them every month at certain date and send them if any files are missing or no bytes (filesize).

Any help is much appreciated.

note: I am running splunk 6.5.3 and thus queries like where(in) does not work for me.

Thanks,
Amit

Tags (1)
0 Karma
1 Solution

to4kawa
Ultra Champion

Sample query:

| makeresults count=1000
| eval Filenames=trim("string_1L_2".(random() % 4),"0")
| streamstats count
| stats count by Filenames
| append [ | makeresults
| eval counter=mvappend("0",mvrange(1,4),"5")
| mvexpand counter
| eval Filenames=trim("string_1L_2".counter,"0")
| table Filenames]
| stats dc(count) as FileExists by Filenames
| eval FileExists=case(FileExists=1,"yes",FileExists=0,"no")

Recommend:

index=123 host=htrstef87 "string_1," "created" NOT client_ip="192.168.17.5" "String_examp_*" "xml.7z.pgp" 
| eval keyword=case(searchmatch("string_1L_2"),"string_1L_2",searchmatch("string_1L_21")
    ,"string_1L_21",searchmatch("string_1L_22"),"string_1L_22",searchmatch("string_1L_23")
    ,"string_1L_23",searchmatch("string_1L_24"),"string_1L_24") 
| eval DateReport=strftime(_time, "%Y-%m") 
| eval Filename=coalesce(FileName,keyword) 
| stats count by DateReport Filename 
| append 
    [| makeresults 
    | eval counter=mvrange(0,5) 
    | mvexpand counter 
    | eval Filename=trim("string_1L_2".counter,"0") 
    | table Filename] 
| stats dc(count) as FileExists by Filename 
| eval FileExists=case(FileExists=1,"yes",FileExists=0,"no")

I'm not sure your original field name. if there is a few typo, please amend it.

View solution in original post

0 Karma

anmolpatel
Builder

I've taken @to4kawa 's initial query and made modification to what you're looking for

| makeresults count=10000 
| eval COMMENT= "Randomly generate file names with the below string names"
| eval File="string_1L_".(random() % 8)
| eval File="string_L_".(random() % 8)

| eval COMMENT= "For filename with string_1L_2 create file in series from 20-28"
| append 
    [| makeresults 
    | eval counter=mvappend("0",mvrange(1,8),"8") 
    | mvexpand counter 
    | eval File=trim("string_1L_2".counter,"1") 
    | table File] 
| stats count by File

| eval COMMENT = " Have replaced searchmatch function with match function using regex to check if you've a file with the particular string. This avoids multiple checks. You can replace the regex .*string_1L_2.* with .*string_1L_ .* and this will match with all values that get generated above, to test further."
| eval FileExists=if(match(File, ".*string_1L_2.*"),"Yes", "No")
| table File FileExists

The eval I've above for File Exists, effectively replaces what you're doing here:

| eval keyword=case(searchmatch("string_1L_2"),"string_1L_2",searchmatch("string_1L_21"),"string_1L_21",searchmatch("string_1L_22"),"string_1L_22",searchmatch("string_1L_23"),"string_1L_23",searchmatch("string_1L_24"),"string_1L_24") 
| eval Filestatus=if(like(keyword, "string_1L%"), "fileFound", "Filenotfound")

You can add in your Filesize check after that.

0 Karma

to4kawa
Ultra Champion

Sample query:

| makeresults count=1000
| eval Filenames=trim("string_1L_2".(random() % 4),"0")
| streamstats count
| stats count by Filenames
| append [ | makeresults
| eval counter=mvappend("0",mvrange(1,4),"5")
| mvexpand counter
| eval Filenames=trim("string_1L_2".counter,"0")
| table Filenames]
| stats dc(count) as FileExists by Filenames
| eval FileExists=case(FileExists=1,"yes",FileExists=0,"no")

Recommend:

index=123 host=htrstef87 "string_1," "created" NOT client_ip="192.168.17.5" "String_examp_*" "xml.7z.pgp" 
| eval keyword=case(searchmatch("string_1L_2"),"string_1L_2",searchmatch("string_1L_21")
    ,"string_1L_21",searchmatch("string_1L_22"),"string_1L_22",searchmatch("string_1L_23")
    ,"string_1L_23",searchmatch("string_1L_24"),"string_1L_24") 
| eval DateReport=strftime(_time, "%Y-%m") 
| eval Filename=coalesce(FileName,keyword) 
| stats count by DateReport Filename 
| append 
    [| makeresults 
    | eval counter=mvrange(0,5) 
    | mvexpand counter 
    | eval Filename=trim("string_1L_2".counter,"0") 
    | table Filename] 
| stats dc(count) as FileExists by Filename 
| eval FileExists=case(FileExists=1,"yes",FileExists=0,"no")

I'm not sure your original field name. if there is a few typo, please amend it.

0 Karma

to4kawa
Ultra Champion
index=123 host=htrstef87 "string_1," "created" NOT client_ip="192.168.17.5" "String_examp_*" "xml.7z.pgp" 
| eval DateReport= strftime(_time, "%Y-%m") 
| stats count(eval(isnull(FileName))) as missing_File_count count(eval(searchmatch("string_1L"))) as File_count by DateReport
0 Karma

asharmaeqfx
Path Finder

This works and displays the same result. But my requirement is there are five strings and i need see from the query and check if they are missing. Currently i need the keywords in a column and one more column shows whether it exists or not

Keywords/Filenames FileExists
string_1L_2 Yes
string_1L_21 No
string_1L_22 Yes
string_1L_23 No
string_1L_25 Yes

0 Karma

anmolpatel
Builder

Write a saved search that executes on a cron schedule ?
The result can then be displayed based on requirement (dashboard panel / email etc.)

0 Karma

asharmaeqfx
Path Finder

yes but i still does not show me missing files. Rather all the files which came fine.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

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