# MOD -- Splunk Language explain and translation (SQL)
###### tags: `By_Ivan`

### CPU monitor

index = cht_mod_os sourcetype="cpu" host="*-HC-*" OR host="*-DHC-*"
| multikv fields PctIdle     				## might have issue
| eval CpuLoad = 100-pctIdle 				## typo?
| stats avg(CpuLoad) as avgLoad by host
| search avgLoad > 80
| eval Load = ceil(avgLoad)
| eval Time=strftime(now(), "%H:%M:%S")
| strcat "MMP " host "CPU avg load " Load "% in hour before " Time SMSText
| strcat "MMP " host " CPU avg load " Load "% in hour before " Time EMailText
| table SMSText, EMailText

multikv means that the event data is stored as a table (stuctured data)
</br>if the same text was put inside SQL database, this will not be easy to process

# 首先將 Idle 時間轉換成 Cpu Load
# 刪選掉 80% 以下的資料
# 計算每個 Host 的平均 Cpu Load 並無條件進位到個位數
# 調整時間format，並用 Strcat 組成 ouptut 用的指令

____

### CSV generation

index = "cht_mod_opsws" source="/opt/cht_mod/logs/opsws/posws_n.txt"
| dedup OfferingId
| eval SuggestedPrices = round(SuggestedPrice, 0)
| eval DiscountedPrice = SuggestedPrices
| eval BillingID = "B"+VideoVendor
| table OfferingId AssetId PreviewName ChineseTitle EnglishTitle Ratings VidoLength Actors Director Summary Bitrate ParentCategoryId FullPath SugestedPrices .....
| outputcsv program.csv

the csv file will be stored at $SPLUNK_HOME/var/run/splunk/csv

# 去除有同一 OfferingId 的資料，Id 代表什麼意義還需研究，似乎是 Linux 系統下的 Id
# 建議售價四捨五入至個位數，並複製出一個 column DiscountedPrice
# VideoVendor 加上一個字母 "B"

#### Some SQL Translation

1. CSV generation

SELECT
    OfferingId, AssetId, PreviewName, ChineseTitle, EnglishTitle, Ratings, VidoLength, Actors, Director,Summary, Bitrate, ParentCategoryId, FullPath,
    SugestedPrices,
    SugestedPrices AS DiscountedPrice,
    UnitPrice, LicensingStart, LicensingEnd, Status, Language, VideoType, Genre, VideoVendor, VideoASP, VideoEncode,
    CONCAT("B", VideoVendor) AS BillingID
FROM(
    SELECT 
        * , ROUND(SugestedPrices, 0) AS SugestedPrices,
        RN=ROW_NUMBER()OVER(PARTITION BY OfferingId ORDER BY OfferingId)
    FROM cht_mod_opsws
    WHERE source="/opt/cht_mod/logs/opsws/posws_n.txt"
)data
WHERE RN=1

____
2. Alert message

SELECT
CONCAT(Server, " ", Services) AS FullSvcName ,
	CONCAT(Server, " ", Services, "No Response @", Time) AS SMSText,
	CONCAT(Server, " ", Services, "No Response @", Time) AS EMailText	
FROM(
	SELECT
		strftime("%Y/%m/%d %H:%M:%S", @timestamp) AS 'Time',
		Server,
		Services
	FROM mw_service_check
	WHERE host="SKC5-DAS-2001" AND _raw LIKE "%TEST Ping No Response%"
)data

___
3. Usage over 80 %

SELECT
	CONCAT(Server, " ", Services) AS FullSvcName ,
	CONCAT(Server, " ", Services, "No Response @", Time) AS SMSText
	CONCAT(Server, " ", Services, "No Response @", Time) AS EMailText
FROM(
	SELECT 
		strftime("%H:%M:%S", @timestamp) AS 'Time',
		REGEXP_SUBSTR (Status, "\d* ") AS 'Usage',
		Server,
		Services
	FROM db.diskgroup
)data
WHERE TO_NUMBER(Usage)>80

___
4. System error unexpected

SELECT 
	CONCAT(Server, " ", Services) AS FullSvcName ,
	CONCAT(Server, " ", Services, "No Response @", Time) AS SMSText
	CONCAT(Server, " ", Services, "No Response @", Time) AS EMailText
FROM(
	SELECT
		strftime("%H:%M:%S", @timestamp) AS 'Time',
		Server,
		Services
	FROM vdb.instance
	WHERE status NOT IN ("OPEN", "OUNTED", "STARTED")
)data