How to select only unique events in a particular time interval with respect to host.

[rohithmn3]

Hi Team,

My search query return 100+ events out of which 60 events belong to host1 and remaining 40 events belong to host2. Now i want to list only unique events based on Config_Name column. I mean combining host1 and host2 can have duplicate events as they belong to different hosts so it's fine, but any single host should not have duplicate events.

Sample Events:
Config_Name=/proj/sasqa/sasdata/APM93/setEnvLogs.cfg.sh Config_Status=OK Config_Name=/proj/sasqa/sasdata/support/operational/sasshare/scripts/start_sasshare.sas Config_Status=OK Config_Name=/app/sas/sashome/SASPlatformObjectFramework/9.3/ImportPackage.ini Config_Status=OK

[gokadroid · Accepted solution]

If you really want the formatting to be the way you mentioned in your question (using list), how about trying this below which will remove the duplicate problem first and then table it out like you want:

index=ao_status sourcetype=checkConfig 
| rex "^.*\sConfig_Name=(?P[^\s]+)\sConfig_Status=(?P[^\s]+)" 
| stats count by cfgName, cfgStatus, host
| stats list(cfgName) as "Config_Name", list(cfgStatus) as "Config_Status" by host

However I would suggest using the below one to keep track of the cfgName and cfgStatus mapping to host intact in all cases for scenarios where the status has more than one value for a particular config (exa. /etc/httpd.cfg having say NOT-OK first and then OK later on):

 index=ao_status sourcetype=checkConfig 
| rex "^.*\sConfig_Name=(?P[^\s]+)\sConfig_Status=(?P[^\s]+)" 
| stats count by cfgName, cfgStatus, host
| table  cfgName , cfgStatus, host
| rename  cfgName as "Config_Name", cfgStatus as "Config_Status"

[rohithmn3]

Current Output:

host    Config_Name Config_Status
host1   /etc/pred.cfg   OK
           /etc/ntp.cfg OK
           /etc/httpd.cfg   OK
           /etc/httpd.cfg   OK

host2   /etc/pred.cfg   OK
         /etc/ntp.cfg   OK
         /etc/ntp.cfg   OK
         /etc/httpd.cfg OK



Expected output:        

host    Config_Name Config_Status
host1   /etc/pred.cfg   OK
           /etc/ntp.cfg OK
           /etc/httpd.cfg   OK

host2   /etc/pred.cfg   OK
           /etc/ntp.cfg OK
          /etc/httpd.cfg    OK

My Search Query:
index=ao_status sourcetype=checkConfig | rex "^.*\sConfig_Name=(?P[^\s]+)\sConfig_Status=(?P[^\s]+)" | stats list(cfgName) as "Config Name", list(cfgStatus) as "Config Status" by host

[niketn]

Following query should do what you are looking for:

<Your Base Search>
| stats list(cfgStatus) as "Config Status" by host, cfgName
| rename cfgName as "Config Name"

Or You can also create a column by combining host and cfgName

<Your Base Search>
| eval Host-ConfigName= host + cfgName
| stats list(cfgStatus) as "Config Status" by Host-ConfigName

[rohithmn3]

**Current Output:**

host    Config_Name Config_Status
host1   /etc/pred.cfg   OK
           /etc/ntp.cfg OK
           /etc/httpd.cfg   OK
          /etc/httpd.cfg    OK

host2   /etc/pred.cfg   OK
            /etc/ntp.cfg    OK
            /etc/ntp.cfg    OK
            /etc/httpd.cfg  OK


**Expected output:**    

    host    Config_Name Config_Status
    host1   /etc/pred.cfg         OK
               /etc/ntp.cfg       OK
              /etc/httpd.cfg          OK

    host2   /etc/pred.cfg       OK
               /etc/ntp.cfg     OK
              /etc/httpd.cfg        OK

---

My Search Query:

index=ao_status sourcetype=checkConfig | rex "^.*\sConfig_Name=(?P[^\s]+)\sConfig_Status=(?P[^\s]+)" | stats list(cfgName) as "Config Name", list(cfgStatus) as "Config Status" by host