Help to write the query

mm12 — Thu, 23 Sep 2021 13:55:37 GMT

Hello,

I have below query. Getting data from dc_nfast index and putting it in test index and using this test index in dashboard. we are not using test dc_nfast index anywhere. I want to refine the query so that it gets data from same index and putting in it same index. Can one advice on this

`comment("Get latest NFAST data")`

index=dc_nfast source=MIDDLEWARE_NFAST_DETECTION NOT "Nfast Version" IN ("NA","[no results]") [
| tstats latest(_time) as latest earliest(_time) as earliest count where index=dc_nfast source=MIDDLEWARE_NFAST_DETECTION host=nfast* earliest=-30d by _time , source , host span=2m
| stats values(count) as count values(earliest) as earliest values(latest) as latest by _time , source , host
| eventstats median(count) as median p5(count) as p5 p95(count) as p95 by source , host
| eval range=(p95-p5)
| eval lower_count=(median-range*3) , upper_count=(median+range*3)
| where count >=lower_count AND count<=upper_count
| eval earliest = earliest - 1 , latest = latest + 1
| dedup source , host sortby - latest
| return 10 earliest latest host source ]

`comment("Get lifecyclestate, country and ipaddress from master asset lists")`
| lookup dc_app_dcmaster_master_asset_ul.csv hostname OUTPUT master_ipaddress as ul_master_ipaddress master_lifeCycleState as ul_master_lifeCycleState master_country as ul_master_country
| lookup dc_app_dcmaster_master_asset_win.csv hostname OUTPUT master_ipaddress as win_master_ipaddress master_lifeCycleState as win_master_lifeCycleState master_country as win_master_country
| lookup dc_app_unicorn_lookup.csv hostName as hostname OUTPUT locationCode
| eval ipaddress=coalesce(ul_master_ipaddress, win_master_ipaddress) , lifeCycleState=coalesce(ul_master_lifeCycleState,win_master_lifeCycleState) , country=coalesce(ul_master_country,win_master_country)
| fillnull value="UNKNOWN" ipaddress lifeCycleState country locationCode

`comment("Join on serial number data from HSMC CSV import")`

| rename "Serial Number" as HSMSerialNumber
| makemv HSMSerialNumber
`comment("Count the number of serial numbers per host")`
| eval HSM_count=mvcount(HSMSerialNumber)
| fillnull value=0 HSM_count
| mvexpand HSMSerialNumber
| join type=left HSMSerialNumber [ search `get_latest_source_in_range(test , /opt/splunk/etc/apps/dc_ta_nfast_inputs/git/master/HSMSplunkFeed/SPLUNK_HSM_Import_Listing.csv)` ]
| rename Country as HSM_country "HSM *" as HSM_* "HSM * *" as HSM_*_
| foreach HSM* [ fillnull value="No_HSM_data" <<FIELD>> ]

`comment("Add EIM service data - creating")`
| lookup app_eim_lookup_eim_basic_extract.csv hostname OUTPUT PLADA_CRITICALITY SERVICE_IT_ORG6 SERVICE_IT_ORG7 IT_SERVICE SERVICE_OWNER SERVICE_OWNER_EMAIL
| fillnull value="UNKNOWN" IT_SERVICE SERVICE_OWNER SERVICE_OWNER_EMAIL PLADA_CRITICALITY SERVICE_IT_ORG6 SERVICE_IT_ORG7
| eval servicedata=mvzip(IT_SERVICE ,SERVICE_OWNER ,"##")
| eval servicedata=mvzip(servicedata,SERVICE_OWNER_EMAIL ,"##")
| eval servicedata=mvzip(servicedata,PLADA_CRITICALITY ,"##")
| eval servicedata=mvzip(servicedata,SERVICE_IT_ORG6 ,"##")
| eval servicedata=mvzip(servicedata,SERVICE_IT_ORG7 ,"##")
| eval servicedata=mvdedup(servicedata)
| fields - PLADA_CRITICALITY SERVICE_IT_ORG6 ISERVICE_IT_ORG7 T_SERVICE SERVICE_OWNER SERVICE_OWNER_EMAIL
| eval servicedata=mvdedup(servicedata)

`comment("Now expand each servicedata into multiple lines where the is multiple values ")`
| mvexpand servicedata

`comment("Recreate the service values")`
| rex field=servicedata "(?<IT_SERVICE>.*?)##(?<SERVICE_OWNER>.*?)##(?<SERVICE_OWNER_EMAIL>.*?)##(?<PLADA_CRITICALITY>.*?)##(?<SERVICE_IT_ORG6>.*?)##(?<SERVICE_IT_ORG7>.*)"

`comment("Run addinfo command to capture info_search_time field")`
| addinfo

`comment("Write out to index")`
| eval _time=now()
| table hostname Combined Nfast* ipaddress lifeCycleState HSM_count country locationCode IT_SERVICE SERVICE_OWNER SERVICE_OWNER_EMAIL PLADA_CRITICALITY SERVICE_IT_ORG6 SERVICE_IT_ORG7 HSMSerialNumber HSM*
| collect index=test source=stash

Thanks

Re: Help to write the query

codebuilder — Thu, 23 Sep 2021 16:23:21 GMT

If your end goal is to gather data for powering a dashboard then using a datamodel seems a better solution. Then you can accelerate it for a given time range if you're looking for a performance increase.

In your existing search you might want to look at your use of table toward the end as it doesn't transform results and looks like collect is going to pull in _raw.

Also, summary indexing as you're doing with collect counts against your license so be careful. Datamodel (or report) acceleration does not cause a hit because you are not indexing new data. Just FYI...

topic Help to write the query in Splunk Search

Help to write the query

Re: Help to write the query