Hi all,
I am setting dashboard and alert where we are trying to alert if there is missing hosts in splunk for more than 24 hours . I am using below query but getting malformed error when running in search although on dashboard its giving result.
| inputlookup data.csv where DECOMMISSIONED=N SUB_ENVIRONMENT!=TEST
| fields ACTIVE_DC APP_NAME DATABASE HOST_NAME APP_NAME DB_VERSION DB_ROLE SUB_ENVIRONMENT
| eval Reference=ABC
| rename HOST_NAME as host
| join type=left host
[ search index=dbecx source="*audit*"
| stats count as SPLEvents latest(_time) as LastSeen by host
| eval age=round((now()-LastSeen)/3600,1)
| eval Status=case(
LastSeen>(now()-(3600*2)),"Low",
LastSeen<(now()-(3600*2+1)) AND LastSeen>(now()-(3600*8)) ,"Medium",
LastSeen<(now()-(3600*8+1)) AND LastSeen>(now()-(3600*24)),"High",
1=1,"Critical")
| convert ctime(LastSeen) timeformat="%d-%m-%Y %H:%M:%S"
| eval Reference="SPL"]
| fields DB_VERSION DATABASE APP_NAME ACTIVE_DC host Status SPLEvents
| rex mode=sed field=host "s/\..*$//g"
| fillnull value=Missing Status
| fillnull value=Null
Can someone help here
have i make anything in the above query
yeah this worked also there is one query with left join so i have to get the db count with creating join on one column but post applying join the numbers are not correct .
| inputlookup abc.csv where DECOMMISSIONED=N ENVIRONMENT=XYZ
| fields ACTIVE_DC APP_NAME HOST_NAME DATABASE DB_VERSION DB_ROLE ENVIRONMENT
| eval Reference=ISD
| rename HOST_NAME as host
| join type=left host
[ search index=cb_* sourcetype="*dbx*"
| stats count as SPLEvents latest(_time) as LastSeen by host
| eval age=round((now()-LastSeen)/3600,1)
| eval Status=case(
LastSeen>(now()-(3600*2)),"Low",
LastSeen<(now()-(3600*2+1)) AND LastSeen>(now()-(3600*8)) ,"Medium",
LastSeen<(now()-(3600*8+1)) AND LastSeen>(now()-(3600*24)),"High",
1=1,"Critical")
| convert ctime(LastSeen) timeformat="%d-%m-%Y %H:%M:%S"
| eval Reference="SPL"]
| fields DB_VERSION APP_NAME DATABASE ACTIVE_DC host Status SPLEvents
| rex mode=sed field=host "s/\..*$//g"
| fillnull value=Missing Status
| fillnull value=Null
| search APP_NAME="*" DATABASE="*" host="*" DB_VERSION="*"
| eval DBStatus=if(SPLEvents="Null","missing","ok")
| search DBStatus="ok"
| stats dc(host) as dbcount
Your dashboard is XML. Because of this, certain characters have to be encoded, e.g. > and < which are encoded as > and < respectively. Try using the open in search button on your dashboard, or if you want to just copy the search from your dashboard source, then decode the encoded characters e.g.
| inputlookup data.csv where DECOMMISSIONED=N SUB_ENVIRONMENT!=TEST
| fields ACTIVE_DC APP_NAME DATABASE HOST_NAME APP_NAME DB_VERSION DB_ROLE SUB_ENVIRONMENT
| eval Reference=ABC
| rename HOST_NAME as host
| join type=left host
[ search index=dbecx source="*audit*"
| stats count as SPLEvents latest(_time) as LastSeen by host
| eval age=round((now()-LastSeen)/3600,1)
| eval Status=case(
LastSeen>(now()-(3600*2)),"Low",
LastSeen<(now()-(3600*2+1)) AND LastSeen>(now()-(3600*8)) ,"Medium",
LastSeen<(now()-(3600*8+1)) AND LastSeen>(now()-(3600*24)),"High",
1=1,"Critical")
| convert ctime(LastSeen) timeformat="%d-%m-%Y %H:%M:%S"
| eval Reference="SPL"]
| fields DB_VERSION DATABASE APP_NAME ACTIVE_DC host Status SPLEvents
| rex mode=sed field=host "s/\..*$//g"
| fillnull value=Missing Status
| fillnull value=Null
The Search & Reporting app doesn't recognize ">" or "<" as ">" and "<", respectively. You have to decode them yourself.